Writing before logoff Triggers

[Guru3i]

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

[Tim...]

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...