Triggers Triggers Motivation Assertions are powerful

Triggers

Triggers: Motivation • Assertions are powerful, but the DBMS often can’t tell when they need to be checked. • Attribute- and tuple-based checks are checked at known times, but are not powerful. • Triggers let the user decide when to check for any condition.

Event-Condition-Action Rules • Another name for “trigger” is event-condition-action (ECA) rule. • Event : typically a type of database modification, e. g. , “insert on Sells. ” • Condition : Any SQL boolean-valued expression. • Action : Any SQL statements.

Example • Using Sells(bar, beer, price) and a unary relation, maintain a list of bars that raise the price of any beer by more than $1. – Let the unary relation be Rip. Off. Bars(bar). CREATE TABLE Sells( beer VARCHAR(10), bar VARCHAR(13), price FLOAT ); CREATE TABLE Rip. Off. Bars( bar VARCHAR(13) );

The Trigger Event: only changes to prices CREATE OR REPLACE TRIGGER Price. Trig AFTER UPDATE OF price ON Sells We need to consider FOR EACH ROW each price change WHEN(new. price > old. price + 1. 00) Condition: BEGIN a raise in price > $1 INSERT INTO Ripoff. Bars Updates let us talk about old and new tuples. VALUES(: new. bar); END Price. Trig; When the price change is great / enough, add the bar to Ripoff. Bars Remark. This and other trigger examples are in ORACLE syntax which differs slightly from standard SQL syntax.

Options: CREATE TRIGGER • CREATE TRIGGER • Or: CREATE OR REPLACE TRIGGER – Useful if there is a trigger with that name and you want to modify the trigger. • If creating the trigger gives: Warning: Trigger created with compilation errors. Execute: show errors to display the errors.

Options: The Event • AFTER can be BEFORE. • UPDATE ON can be DELETE ON or INSERT ON. • And UPDATE ON can be UPDATE …OF… ON mentioning a particular attribute in relation.

Options: FOR EACH ROW • Triggers are either “row-level” or “statement-level. ” • FOR EACH ROW indicates row-level; its absence indicates statement-level. • Row level triggers : execute once for each modified tuple. • Statement-level triggers : execute once for an SQL statement, regardless of how many tuples are modified.

Row Triggers In ORACLE • For an update trigger, the old attribute value can be accessed using : old. • and the new attribute value can be accessed using : new. • For an insert trigger, only : new. can be used. • For a delete trigger only : old. can be used. • In WHEN clause of the trigger use old. , new. (i. e. no colon : )

Options: The Condition • Any boolean-valued condition. • Evaluated on the database as it would exist before or after the triggering event, depending on whether BEFORE or AFTER is used.

Options: The Action • Surround by BEGIN. . . END.

Another Example CREATE TABLE emp ( empno INT, ename VARCHAR(30), deptno INT, sal FLOAT, comm FLOAT ); • The following is a before row-level trigger that calculates the commission of every new employee belonging to department 30 that is inserted into the emp table. CREATE OR REPLACE TRIGGER emp_comm_trig BEFORE INSERT ON emp FOR EACH ROW BEGIN IF : NEW. deptno = 30 THEN : NEW. comm : = : NEW. sal *. 4; END IF; END; /

Let’s trigger INSERT INTO emp VALUES (9005, 'ROBERS', 3000, NULL); INSERT INTO emp VALUES (9006, 'ALLEN', 30, 4500, NULL); SELECT * FROM emp WHERE empno IN (9005, 9006); EMPNO ENAME DEPTNO SAL COMM -----------------9005 ROBERS 30 3000 1200 9006 ALLEN 30 4500 1800

Miscellaneous about Triggers • Multiple Trigger Events: You may specify up to three triggering events using the keyword OR. Here are some examples: . . . INSERT ON R. . . INSERT OR DELETE OR UPDATE ON R. . . • Restrictions on include: – You can’t modify the same relation whose modification is the event triggering the trigger. – You can’t modify a relation which is the “parent” of the triggering relation in a foreign-key constraint.

Miscellaneous about Triggers • Viewing Defined Triggers – To view a list of all defined triggers, use: SELECT trigger_name FROM user_triggers; – For seeing the code: SELECT text FROM user_source Has to be WHERE name = 'PRICETRIG' uppercase. ORDER BY line; • Dropping Triggers DROP TRIGGER ; • Disabling or Enabling Triggers ALTER TRIGGER {DISABLE|ENABLE};

Aborting Triggers with Errors • Triggers are often be used to enforce constraints. – Using built-in function RAISE_APPLICATION_ERROR. • The action that activated the trigger (insert, update, or delete) would be aborted. – E. g, the following trigger enforces the constraint Person. age >= 0: CREATE TABLE Person (age INT); CREATE TRIGGER Person. Check. Age AFTER INSERT OR UPDATE OF age ON Person FOR EACH ROW BEGIN IF (: new. age < 0) THEN RAISE_APPLICATION_ERROR(-20000, 'no negative age allowed'); END IF; END; /

Aborting Triggers with Errors (Cont’d) • If we attempted to execute the insertion: INSERT INTO Person VALUES (-3); we would get the error message: ERROR at line 1: ORA-20000: no negative age allowed ORA-06512: at "MYNAME. PERSONCHECKAGE", line 3 ORA-04088: error during execution of trigger 'MYNAME. PERSONCHECKAGE' and nothing would be inserted.

Statement-Level Trigger • Whenever an insert, update, or delete operation occurs on the emp table, a row is added to the empauditlog table recording the date, user, and action. • First let’s create the empauditlog table: CREATE TABLE empauditlog ( audit_date DATE, audit_user VARCHAR 2(20), audit_desc VARCHAR 2(20) );

Now the trigger CREATE OR REPLACE TRIGGER emp_audit_trig AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE v_action VARCHAR 2(20); BEGIN IF INSERTING THEN v_action : = 'Added employee(s)'; ELSIF UPDATING THEN v_action : = 'Updated employee(s)'; ELSIF DELETING THEN v_action : = 'Deleted employee(s)'; END IF; INSERT INTO empauditlog VALUES (SYSDATE, USER, v_action); END; /

Let’s trigger it… INSERT INTO emp(empno, ename, deptno) VALUES (9001, 'SMITH', 50); INSERT INTO emp(empno, ename, deptno) VALUES (9002, 'JONES', 50); UPDATE emp SET ename = 'SMITH BROWN' WHERE empno=9001; DELETE FROM emp WHERE empno IN (9001, 9002); SELECT TO_CHAR(AUDIT_DATE, 'DD-MON-YY HH 24: MI: SS') AS "AUDIT DATE", audit_user, audit_desc FROM empauditlog ORDER BY 1 ASC; AUDIT DATE ---------08 -FEB-08 09: 43: 02 AUDIT_USER ----------THOMO AUDIT_DESC ----------Added employee(s) Deleted employee(s) Updated employee(s) Added employee(s)