2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

Can we use TCL commands in a database trigger

Can we use TCL commands in a database trigger .
userimage

We cannot use TCL like commit or rollback in Triggers.A Trigger may not issue any transactional control statements-COMMIT, ROLLBACK or SAVEPOINT. The trigger is fired as part of the execution of the triggering statement and is in the same transaction as the triggering statement. When the triggering statement is committed or rollback, the work in the trigger is committed or rollback as well.

But by using pragma autonomous_transaction we can give TCL commands in triggers.

Ex:

create or replace

trigger UPDATEEMP1 before insert  on EMP1 for each row

declare

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

IF (TO_CHAR(SYSDATE,'DAY')) IN ('SUNDAY','SATURDAY ')

THEN

RAISE_APPLICATION_ERROR(-20222,'site will not work on weekends');

ELSE

 

insert into EMP2 values(:new.EMPNO,:new.ENAME,:new.job,:new.MGR,:new.HIREDATE,:new.SAL,:new.COMM,:new.DEPTNO);

commit;

END IF;

end;

 

If we create a trigger like above  and

insert into emp1 select * from emp where empno=7369;

then it will insert record into emp2 and committed for emp2. But emp1 is not committed .

Now  if you rollback emp1 new record will be removed and  emp2 new record  will not be removed.

Answer is