Writing before logoff Triggers

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Writing before logoff Triggers

Postby Guru3i » Wed Jan 06, 2010 3:57 pm

Hi Tim,

Greetings and a happy new year to you.

How do we write a before logoff trigger for a schema level.

I want to update a particular table in the schema say scott. I would like to update a column of a table before the user logs off. How do i achieve this. Please help me out and give your views on this.

Also is the same way to be approached for writing a DML on before logon to the database. I tried this piece of code but it doesnt seems to be working for me.

Code: Select all
 create or replace trigger tz_trg_upd
  after logon on schema
  begin
   insert into t3 values(01,'rnd after login');
  end;
/


Please help me out to achieve this, thanks in advance.
Thanks
Guru3i
Senior Member
 
Posts: 95
Joined: Fri Aug 24, 2007 7:17 am

Re: Writing before logoff Triggers

Postby Tim... » Thu Jan 07, 2010 5:27 pm

Hi.

Please post your questions in the correct forum. This is not a suggestion/comment, it is a development issue, so it should be posted there. I've moved it to the relevant location. :)

To your issue...

You have the correct idea as far as the trigger go, but you need to be able to commit the changes and this requires that you make the code an autonomous transaction.

http://www.oracle-base.com/articles/mis ... ctions.php

You would be better off putting your code into a package like this:

Code: Select all
CREATE OR REPLACE PACKAGE system_triggers_pkg AS

PROCEDURE logon;
PROCEDURE logoff;

END system_triggers_pkg;
/

CREATE OR REPLACE PACKAGE BODY system_triggers_pkg AS

PROCEDURE logon IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO ....
  COMMIT;
END logon;

PROCEDURE logoff IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE ....
  COMMIT;
END logoff;

END system_triggers_pkg;
/


When you have coded your package to do the relevant inserts and updates, you can create your triggers.

Code: Select all
CREATE OR REPLACE TRIGGER logon_trg
AFTER LOGON ON SCHEMA
CALL system_trigger_pkg.logon;
/

CREATE OR REPLACE TRIGGER logoff_trg
BEFORE LOGOFF ON SCHEMA
CALL system_trigger_pkg.logoff;
/


Remember, the logoff trigger will only fire if the session disconnects properly. If a connection dies or is killed it will not fire.

If you are still having problems, can you be more specific and post error messages. Say thing like, "but it doesnt seems to be working for me" is rather vague. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 2 guests

cron