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
Trigger Syntax (in ORACLE)

• Below is the syntax for creating a trigger in
  ORACLE (which differs slightly from standard SQL
  syntax)
• CREATE OR REPLACE TRIGGER lttrigger_namegt
• BEFOREAFTER INSERTDELETEUPDATE ON
  lttable_namegt
• FOR EACH ROW
• WHEN (lttrigger_conditiongt)
• BEGIN
• lttrigger_bodygt
• END lttrigger_namegt
• /
• 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 ...

15
Restrictions

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

16
Miscellaneous about Triggers

• Displaying Trigger Definition Errors
• If you get a message Warning Trigger created
  with compilation errors. you can see the error
  messages by typing
• SHOW ERRORS TRIGGER lttrigger_namegt
• Alternatively, you can type, SHOW ERRORS to see
  the most recent compilation error.
• Note that the reported line numbers where the
  errors occur are not accurate.
• 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

Has to be uppercase.
17
Miscellaneous (Contd)

• Dropping Triggers
• DROP TRIGGER lttrigger_namegt
• Disabling or Enabling Triggers
• ALTER TRIGGER lttrigger_namegt DISABLEENABLE

18
Aborting Triggers with Errors

• Triggers can often be used to enforce
  constraints. The WHEN clause or body of the
  trigger can check for the violation of certain
  conditions and signal an error accordingly using
  the ORACLE built-in function RAISE_APPLICATION_ERR
  OR.
• The action that activated the trigger (insert,
  update, or delete) would be aborted. For example,
  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
• /

19
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.
• In general, the effects of both the trigger and
  the triggering statement are rolled back.

20
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)
• )

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

22
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)