Database Triggers SessionV Database Triggers Is a stored

Database Triggers Session-V

Database Triggers Is a stored procedure that are implicitly executed when an insert, update or delete statement is issued against the associated tables.

Commonly Used Prevent Invalid Transactions. l Enforce complex security authorizations. l Maintain Replicate Tables. l

DATABASE TRIGGERS Max 12 Triggers for a Table

3 Parts of a trigger l l l Triggering Event. Trigger Constraint (optional). Trigger Action.

Triggering Event is the SQL statement that causes a trigger to be fired. l event can be an INSERT, UPDATE, DELETE. l

Triggering Restriction is an option available for triggers that are fired for each row.

Triggering Action is the procedure (PL/SQL Block) that contain SQL statements.

Syntax Create or Replace Trigger <trigger_name> [before/after] [insert/update/delete] ON <table_name> [when <condition];

Types of Triggers l l Row Level Triggers Statement Triggers

Row Triggers A Row Trigger is fired each time the table is affected by the triggering Statement.

Statement Triggers is fired once on behalf of the Triggering Statement.

Before Vs After

Before Triggers execute the trigger action before the triggering statement

After Triggers execute the trigger action after the triggering statement

CREATE OR REPLACE TRIGGER T 3 BEFORE INSERT ON EMP BEGIN DBMS_OUTPUT. PUT_LINE ('BEFORE INSERT TRIGGER FIRED'); END;

CREATE OR REPLACE TRIGGER T 4 AFTER INSERT ON EMP BEGIN DBMS_OUTPUT. PUT_LINE ('AFTER INSERT TRIGGER FIRED'); END;

CREATE OR REPLACE TRIGGER T 6 Before Insert on Emp For Each Row Declare A number; BEGIN SELECT COUNT(*) INTO A FROM EMP WHERE EMPNO=: NEW. EMPNO; IF A>0 THEN RAISE_APPLICATION_ERROR (-20001, 'DUPLICATE RECORDS FOUND'); END IF; END;

CREATE OR REPLACE TRIGGER T 6 BEFORE delete ON Bank FOR EACH ROW BEGIN delete from Trans where Accno=: old. Accno; end;

create or replace trigger tt before delete or insert or update of job on emp begin raise_application_error (-20230, 'ACCESS DENIED'); end;

create or replace trigger tt before delete or insert or update of job on emp begin if user<>'RAJ' then raise_application_error (-20230, 'ACCESS DENIED'); End if; End;

create or replace trigger tt before update of job on emp for each row when (new. job='PRESIDENT') begin raise_application_error (-20230, 'ACCESS DENIED'); end;

Create or Replace Trigger print_salary_changes Before Delete or Insert or Update on emp For Each Row WHEN (new. empno>0) Declare sal_diff number; Begin sal_diff : =new. sal - old. sal; dbms_output. put(‘old salary: ‘||: old. sal); dbms_output. put(‘new salary: ‘||: new. sal); dbms_output. put_line(‘difference ‘||sal_diff); end; /

INSTEAD OF TRIGGERS

The Instead of option in the create trigger statement is an alternative to the BEFORE and AFTER Options. This type of trigger provides a transparent way of modifying views that cannot be modified directly through INSERT/UPDATE/DELETE statement

l Oracle fires the trigger instead of executing the trigger statement. The trigger performs update, insert, or delete operations directly on underlying tables. l Users write normal update, insert, and delete statements against the view and the instead of trigger works invisibly in the background to make the right actions take place.

Views cannot be modified by using UPDATE, INSERT or DELETE statements, if the view query contains any one of the following constructs: l Set Operators l Group Functions l GROUP BY , CONNECT BY , or START WITH Clauses l the DISTINCT operator l Joins (a subset of join views are updateable

Create a View Create View Employee as select deptno, ename, job from emp, dept where deptno = emp. deptno; Create or replace Trigger t 1 INSTEAD of Update on employee for each row begin update emp set job = : new. job where ename = : new. ename; end;

Update employee set job = ‘MANAGER’ where ename = ‘SMITH’

Nested Blocks Declare mainvar Varchar 2(10) : = ‘India’; begin Dbms_output. put_line(mainvar); declare mainvar Varchar 2(10) : = ‘South’ begin Dbms_output. put_line(mainvar); end;

<<main>> Declare mainvar Varchar 2(10) : = ‘India’; begin Dbms_output. put_line(mainvar); declare mainvar Varchar 2(10) : = ‘South’ begin Dbms_output. put_line(mainvar||main var); end;

<<main>> Declare mainvar Varchar 2(10) : = ‘India’; begin Dbms_output. put_line(mainvar); <<SOUTH>> declare mainvar Varchar 2(10) : = ‘South’ begin Dbms_output. put_line(mainvar||main var); end; GOTO SOUTH;

Using NULL Statement Declare mainvar Varchar 2(10) : = ‘India’; begin Goto Ending. Prg; Dbms_output. put_line(mainvar); <<Ending. Prg>> Null; end;