Triggers

1
Triggers
2
Triggers Motivation

• Assertions are powerful, but the DBMS often cant
  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.

3
Event-Condition-Action Rules

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

4
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 RipOffBars(bar).
• CREATE TABLE Sells(
• beer VARCHAR(10),
• bar VARCHAR(13),
• price FLOAT
• )
• CREATE TABLE RipOffBars(
• bar VARCHAR(13)
• )

5
The Trigger
Event only changes to prices

• CREATE OR REPLACE TRIGGER PriceTrig
• AFTER UPDATE OF price ON Sells
• FOR EACH ROW
• WHEN(new.price gt old.price 1.00)
• BEGIN
• INSERT INTO RipoffBars
• VALUES(new.bar)
• END PriceTrig
• /
• Remark. This and other trigger examples are in
  ORACLE syntax which differs slightly from
  standard SQL syntax.

We need to consider each price change
Condition a raise in price gt 1 Updates let us
talk about old and new tuples.
When the price change is great enough, add the
bar to RipoffBars
6
Options CREATE TRIGGER

• CREATE TRIGGER ltnamegt
• Or
• CREATE OR REPLACE TRIGGER ltnamegt
• 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.

7
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.

8
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.

9
Row Triggers

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

10
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.

11
Options The Action

• Surround by BEGIN . . . END.

12
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
• /

13
Lets trigger

• INSERT INTO emp VALUES (9005,'ROBERS',30,
  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

14
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 lttrigger_bodygt include
• You cant modify the same relation whose
  modification is the event triggering the trigger.
  
• You cant modify a relation which is the parent
  of the triggering relation in a foreign-key
  constraint.

15
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
• WHERE name 'PRICETRIG'
• ORDER BY line
• Dropping Triggers
• DROP TRIGGER lttrigger_namegt
• Disabling or Enabling Triggers
• ALTER TRIGGER lttrigger_namegt DISABLEENABLE

Has to be uppercase.
16
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 gt 0
• CREATE TABLE Person (age INT)
• CREATE TRIGGER PersonCheckAge
• AFTER INSERT OR UPDATE OF age ON Person
• FOR EACH ROW
• BEGIN
• IF (new.age lt 0) THEN
• RAISE_APPLICATION_ERROR(-20000, 'no
  negative age allowed')
• END IF
• END
• /

17
Aborting Triggers with Errors (Contd)

• 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.

18
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 lets create the empauditlog table
• CREATE TABLE empauditlog (
• audit_date DATE,
• audit_user VARCHAR2(20),
• audit_desc VARCHAR2(20)
• )

19
Now the trigger

• CREATE OR REPLACE TRIGGER emp_audit_trig
• AFTER INSERT OR UPDATE OR DELETE ON emp
• DECLARE
• v_action VARCHAR2(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
• /

20
Lets 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 empno9001
• DELETE FROM emp WHERE empno IN (9001, 9002)
• SELECT TO_CHAR(AUDIT_DATE,'DD-MON-YY HH24MISS')
  AS "AUDIT DATE",
• audit_user, audit_desc
• FROM empauditlog
• ORDER BY 1 ASC
• AUDIT DATE AUDIT_USER
  AUDIT_DESC
• ------------------ --------------------
  --------------------
• 08-FEB-08 094302 THOMO Added
  employee(s)
• 08-FEB-08 094302 THOMO Deleted
  employee(s)
• 08-FEB-08 094302 THOMO Updated
  employee(s)