trigger dbms

1. SHREE GANESH SEMINAR ON:- DATABASE TRIGGERS SUBMMITED TO:- SUBMMITED BY Kuldeep Kumar PRIYANKA Ma’m Mca 3 rd sem 1

2. Contents- 2

3. INTRODUCTION A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. 3

4. The events that fire a trigger Event 1 Event 2 Event 3 Event 4 • DML statements • DDL statements • System events • User events 4

5. 6

6. The need and the usage Audit changes Enforce business rules Execute business rules. Enhance performance 8

7. Major Features triggers do not accept parameters or arguments . triggers cannot perform commit or rollback operations . triggers are normally slow 9

8. Type of trigger Row Triggers and Statement Trigger BEFORE and AFTER Triggers System Events and User Events 10

9. Row Triggers and Statement Trigger • A row trigger is fired Row Triggers each time the table is affected by the triggering statement. • A statement trigger is Statement fired once on behalf Triggers of the triggering statement. 11

10. BEFORE and AFTER Triggers BEFORE triggers run the trigger action before the triggering Before AFTER statement is run. situations: To eliminate unnecessary processing To derive specific column values. • AFTER triggers run the trigger action after the triggering statement is run. 13

11. Triggers on System Events and User events System Events Database startup and shutdown Data Guard role transitions Server error message events User Events User logon and logoff DDL statements DML statements Exp. DBMS_AQ Package database level 15

12. Parts of a Trigger A triggering A trigger A trigger event or restriction action statement 16

13. 17

14. THE TRIGGERING EVENT OR STATMENT A CREATE, ALTER, or DROP statement on any schema object An INSERT, UPDATE, or DELETE statement on a specific table A database startup or instance shutdown A specific error message or any error message A user logon or logoff 18

15. Trigger Restriction Return true and false Exmp :- new.parts_on_hand < new.reorder_point 19

16. Trigger Action Depends of true and false of trigger restriction. A trigger action is the procedure (PL/SQL block, Java program, or C callout) that contains the SQL statements and code to be run when the following events occur . 20

17. Trigger Mode • An enabled trigger runs its trigger action if a triggering statement is issued and the Enabled trigger restriction (if any) evaluates to true • A disabled trigger does not run its trigger action, even if a triggering statement is issued and the trigger restriction (if any) Disabled would evaluate to false. ALTER TRIGGER <Trigger Name> DISABLE|ENABLE 21

18. Creating a Database Trigger Name of the trigger Table to be associated with When trigger is to be fired - before or after Command that invokes the trigger - UPDATE, DELETE, or INSERT Whether row-level trigger or not Condition to filter rows. PL/SQL block that is to be executed when trigger is fired. 22

19. General Structure CREATE [OR REPLACE] TRIGGER trigger_name BEFORE (or AFTER) INSERT OR UPDATE [OF COLUMNS] OR DELETE ON tablename [FOR EACH ROW [WHEN (condition)]] BEGIN END; 23

20. Trigger Syntax CREATE TRIGGER <triggerName> BEFORE|AFTER INSERT|DELETE|UPDATE [OF <columnList>] ON <tableName>|<viewName> [REFERENCING [OLD AS <oldName>] [NEW AS <newName>]] [FOR EACH ROW] (default is “FOR EACH STATEMENT”) [WHEN (<condition>)] <PSM body>; 24

21. Example 1: CREATE OR REPLACE TRIGGER PERSON_INSERT_BEFORE BEFORE INSERT ON EMP BEGIN DBMS_OUTPUT.PUT_LINE(’BEFORE INSERT OF ’ || :NEW.NAME); END; 25

22. Example 2: CREATE OR REPLACE TRIGGER Print_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON emp WHEN (EMPNO > 0) DECLARE sal_diff number; BEGIN sal_diff := :NEW.SAL - :OLD.SAL; dbms_output.put_line('Old salary: ' ||:OLD.sal); dbms_output.put_line('New salary: ' || :NEW.sal); dbms_output.put_line(' Difference ' || sal_diff); END; / 27

23. 32