Search code examples
oracleplsqldatabase-trigger

Can i have trigger for both 'Before' and 'After' events?

I tried the above scenario by the following piece of code

CREATE OR REPLACE TRIGGER test_bf_af2
AFTER LOGON ON SCHEMA and 
BEFORE LOGOFF ON SCHEMA

BEGIN
  --some logic
END;
/

but gave me error saying

Error report: ORA-04079: invalid trigger specification

  1. 00000 - "invalid trigger specification"

*Cause: The create TRIGGER statement is invalid.

*Action: Check the statement for correct syntax.

So, is it possible to have both types or not? if yes, how can I achieve it?

Solution

  • With a regular DML trigger tt is possible to have multiple events (INSERT, UPDATE) in the same trigger. However, the timing must be the same: either BEFORE or AFTER.

    It is the same with system triggers. As Oracle supports neither BEFORE LOGON nor AFTER LOGOFF (for obvious reasons) you need two separate triggers.