Triggers Lesson 10 Triggers SkillBuilders, Inc. SKILLBUILDERS SkillBuilders, Inc. V2.1

Triggers 10.1 Lesson 10 Triggers © 2006 SkillBuilders, Inc. © 2006 SkillBuilders, Inc. SKILLBUILDERS V2.1 Triggers 10.2 10.2 Lesson Objecti...
Author: Jacob Scott
1 downloads 0 Views 130KB Size
Triggers

10.1

Lesson 10 Triggers

© 2006 SkillBuilders, Inc.

© 2006 SkillBuilders, Inc.

SKILLBUILDERS

V2.1

Triggers

10.2

10.2

Lesson Objectives ¾ Triggers concepts ¾ How to create DML triggers ¾ Writing triggers ¾ ¾

Using OLD and NEW Reference Variables Trigger attributes

¾ Autonomous Triggers ¾ Instead Of Triggers ¾ Security issues ¾ Disable and enable triggers © 2006 SkillBuilders, Inc.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.3

10.3

Trigger Concepts ¾ Triggers execute automatically upon a triggering statement or event ¾ Triggers can ¾ ¾ ¾

rollback triggering statement access values from the affected rows call procedures and functions

¾ Triggers cannot be disabled without privilege ¾ Common used for auditing and business rule enforcement among others © 2006 SkillBuilders, Inc.

Triggers are PL/SQL programs that automatically fire (i.e. execute) when: ¾

a DML statement, such as an UPDATE, INSERT or DELETE statement occurs on a table

¾

a system event, such as SHUTDOWN, STARTUP or SERVERERROR occurs

¾

a user event, such as LOGON or LOGOFF occurs

¾

a DDL statement, such as CREATE, DROP or ALTER, occurs

A trigger can: ¾

Cause a rollback of a triggering statement (use the RAISE_APPLICATION_ERROR procedure).

¾

Access values in the affected rows (use the NEW and OLD reference variables)

¾

Call procedures and functions.

continued…

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.4

Common Trigger Uses ¾ Auditing - Who did what to my table when? Triggers can be used to store updated data, user and date/time information in an audit table. ¾ Complex Business Rules - Examples: Deleted customers must be moved to a customer history table. Updates to the stock table are allowed only during market hours. ¾ Derived Value Generation - Triggers can easily calculate derived values (e.g. SUM’s, AVG’s) and store in the triggered table or another table. ¾ Replication - Although Oracle’s built-in SNAPSHOTs handle most replication needs, triggers can be used. Helpful if immediate updates to the replicated data is required. ¾ ON DELETE SET NULL - This refers to the ability to set a foreign key column value to null in the event the parent row is deleted. ¾ UPDATE CASCADE - Changing a parent key with dependents is normally rejected. Triggers can be built to provide support for this operation. This allows you to cause all dependent rows related to a parent key to be updated if the parent key is updated.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.5

10.5

Trigger Execution Oracle Server 1.

BEFORE triggers

2.

CONSTRAINTS

Application Code INSERT INTO table… DELETE FROM table… UPDATE table... 3. Table Updates

4.

AFTER triggers

© 2006 SkillBuilders, Inc.

BEFORE triggers execute before the constraints and actual updates to the table. Commonly used to: ¾ set or modify column values being inserted or updated ¾ check complex security rules (e.g. updates limited to time of day) ¾ enforce business application rules. ¾ It is more efficient to create a BEFORE trigger if the trigger logic will potentially raise an exception to reject the triggering statement. This is because the work done by the constraints and table update has not yet been done so there is no work to undo! AFTER triggers execute after BEFORE triggers, constraint checking and the updates to the table. AFTER triggers are commonly used for: ¾ Auditing ¾ Derived value generation (if the derived value is stored into another table, other than the table the trigger is executing for. If the derived value is to be store in the table the trigger is executing for, the trigger must be defined as a BEFORE trigger.) ¾ Replication

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.6

10.6

Create Trigger Syntax CREATE CREATE [OR [OR REPLACE] REPLACE] TRIGGER TRIGGER [schema.]trigger [schema.]trigger {BEFORE | AFTER | INSTEAD OF} {BEFORE | AFTER | INSTEAD OF} {DELETE {DELETE OR OR INSERT INSERT OR OR UPDATE UPDATE [OF [OF column column [, [, column] column] ...]} ...]} ON ON [schema.]table [schema.]table || DATABASE DATABASE || schema schema [FOR EACH ROW | STATEMENT} [FOR EACH ROW | STATEMENT} ]] [WHEN [WHEN (condition)] (condition)] ]] [declare] [declare] begin begin [exception] [exception] end; end; © 2006 SkillBuilders, Inc.

BEFORE | AFTER: This specifies when the trigger should execute before the constraints and table update, or after the constraints and table update. INSTEAD OF: This specifies the creation of a new (V8) type of trigger, called and “instead of” trigger that executes instead of the triggering statement. Commonly used to affect the UPDATE of views created from multiple tables. DELETE or INSERT or UPDATE: This specifies what type of DML operation should fire the trigger. One, two or all three can be chosen. Note that UPDATE supports specification of one or more columns. This limits trigger execution to only when those columns are updated. ON table: This specifies what table the trigger is defined on. ON DATABASE: This is used to specify that the trigger is for a system event (STARTUP, SHUTDOWN, SERVERERROR) ON schema: This is used to specify a specific schema for which a trigger is to be fired FOR EACH ROW | STATEMENT: Define either a row trigger or a statement trigger. Row triggers execute once for each row effected by the triggering statement and have access to the column values. Statement trigger is default. WHEN condition: Optionally specify a condition that controls trigger execution. Trigger executes if the condition is TRUE. This can increase performance by preventing unneeded executions of the trigger. Can only be used with ROW triggers.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.7

10.7

Row Trigger CREATE CREATE OR OR REPLACE REPLACE TRIGGER TRIGGER after_customer after_customer AFTER DELETE ON customer AFTER DELETE ON customer FOR FOR EACH EACH ROW ROW begin begin INSERT INSERT INTO INTO cust_history cust_history (cust_no, lastname, (cust_no, lastname, firstname) firstname) VALUES VALUES (:old.cust_no, (:old.cust_no, :old.lastname, :old.lastname, :old.firstname); :old.firstname); end; end;

¾ :OLD provides access to deleted row values © 2006 SkillBuilders, Inc.

Row-level triggers: ¾ execute once for each row effected by the triggering statement and ¾ have access to the column values via :NEW and :OLD reference variables This ROW trigger executes if a DELETE occurs against the CUSTOMER table. It executes AFTER constraints and table modification. It copies the deleted row (only a few columns for brevity) into the CUST_HISTORY table by referring to the :OLD variables in the INSERT statement VALUES clause. We will discuss :OLD and :NEW in detail in the next few pages.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.8

10.8

:OLD Reference Variable ¾ :OLD.column-name ¾ ¾ ¾

Contains value before update NULL for INSERT trigger Only valid in ROW trigger

¾ Cannot be modified ¾

Use :NEW to change column values

¾ Commonly used in UPDATE or DELETE triggers ¾

e.g. Maintain audit trail of old values

© 2006 SkillBuilders, Inc.

Use the :OLD reference variable to refer to the old (or current) value in the column, prior to the update. Commonly used to keep an audit trail of new values in an INSERT, both old and new values in an UPDATE, or just the old values in a DELETE. Not valid in STATEMENT triggers. Though :OLD can be referenced in an INSERT trigger, it will always be NULL; there is no old value during an INSERT operation.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.9

10.9

:NEW Reference Variable... ¾ :NEW.column-name contains the value to be inserted or updated ¾ :NEW can be modified in a BEFORE trigger ¾ :NEW can be referenced in AFTER trigger ¾ Commonly used in INSERT and UPDATE triggers ¾ :NEW is NULL in DELETE triggers ¾ Only valid in ROW triggers © 2006 SkillBuilders, Inc.

Use :NEW variables in ROW triggers (not available in STATEMENT triggers) to access the new column values being inserted or updated into a table. The :NEW variable is equal to the :OLD variable if the column is not referenced in the UPDATE SET clause. Modifying a :NEW variable in a BEFORE trigger results in the modified value being stored in the database column. This is invalid in an AFTER trigger, since the update has already occurred. Note that any column can be updated in this fashion, barring any constraint or AFTER trigger violations. The :NEW variable can be referenced in an AFTER trigger however. The :NEW variable is commonly used in INSERT and UPDATE triggers. :NEW is NULL in DELETE triggers.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.10

10.10

... :NEW Reference Variable

CREATE CREATE OR OR REPLACE REPLACE TRIGGER TRIGGER before_customer before_customer BEFORE UPDATE OR INSERT BEFORE UPDATE OR INSERT ON ON customer customer FOR FOR EACH EACH ROW ROW begin begin /* /* convert convert character character values values to to upper upper case case */ */ :new.lastname :new.lastname := := upper( upper( :new.lastname :new.lastname ); ); :new.firstname :new.firstname := := upper( upper( :new.firstname :new.firstname ); ); end; end;

© 2006 SkillBuilders, Inc.

This example shows that by modifying a :NEW reference variable in a BEFORE trigger, you can change the value put into the table. Here we use the UPPER function to convert the character strings to upper case.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.11

10.11

Trigger Attributes... CREATE CREATE OR OR REPLACE REPLACE TRIGGER TRIGGER before_customer before_customer BEFORE UPDATE OR BEFORE UPDATE OR INSERT INSERT OR OR DELETE DELETE ON ON customer customer FOR EACH ROW FOR EACH ROW begin begin IF IF INSERTING INSERTING THEN THEN INSERT INSERT INTO INTO cust_history cust_history (cust_no, (cust_no, lastname, lastname, firstname) firstname) VALUES VALUES (:new.cust_no, (:new.cust_no, :new.lastname, :new.lastname, :new.firstname); :new.firstname); ELSIF UPDATING ELSIF UPDATING THEN THEN UPDATE UPDATE cust_history cust_history SET SET lastname lastname == :new.lastname, :new.lastname, firstname = firstname = :new.firstname :new.firstname WHERE WHERE cust_no cust_no == :new.cust_no; :new.cust_no; © 2006 SkillBuilders, Inc.

continued. . .

There are three Boolean trigger attributes which allow us to determine what DML activity has caused the trigger to execute: ¾ INSERTING - True if the trigger is executing due to an INSERT operation. ¾ UPDATING - True if the trigger is executing due to an UPDATE operation. ¾ DELETING - True if the trigger is executing due to a DELETE operation. Trigger attributes can be used in ROW or STATEMENT triggers.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.12

10.12

...Trigger Attributes ELSIF ELSIF DELETING DELETING THEN THEN begin begin /* /* insure insure history history is is there.... there.... */ */ UPDATE UPDATE cust_history cust_history SET SET lastname lastname == :old.lastname, :old.lastname, firstname = firstname = :old.firstname :old.firstname WHERE cust_no WHERE cust_no == :old.cust_no; :old.cust_no; end; end; END END IF; IF; end; end;

© 2006 SkillBuilders, Inc.

This trigger allowed us to "under the covers" handle logging changes to the CUSTOMER table in our CUST_HISTORY table. The other programming users are freed from this responsibility, we ensure it is always done since Oracle will automatically invoke this trigger.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.13

10.13

Audit Trigger CREATE CREATE OR OR REPLACE REPLACE TRIGGER TRIGGER audit_customer audit_customer AFTER UPDATE OR INSERT AFTER UPDATE OR INSERT OR OR DELETE DELETE ON ON customer customer FOR EACH ROW FOR EACH ROW begin begin IF IF INSERTING INSERTING THEN THEN INSERT INTO class_audit INSERT INTO class_audit (c_user, (c_user, c_date, c_date, operation, operation, new_data) new_data) VALUES VALUES (user, (user, sysdate, sysdate, 'insert', 'insert', to_char(:new.cust_no)|| to_char(:new.cust_no)|| :new.lastname :new.lastname ); ); END END IF; IF; end; end;

© 2006 SkillBuilders, Inc.

Let’s say you need to audit (i.e. record) all inserts to the CUSTOMERS table. One solution would be to create a table similar to this: create table class_audit ( c_user

varchar2(30) default user,

c_date

date default sysdate,

operation

varchar2(30),

old_data

varchar2(2000),

new_data

varchar2(2000)

); Then create a trigger like the one above that records all INSERT activity in the CLASS_AUDIT table. Of course we could expand this trigger to work like the previous example and track all changes (INSERT, DELETE, UPDATE). We would probably want to add a column to our class_audit table to reflect the kind of modification made.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.14

10.14

Derived Value Trigger CREATE CREATE OR OR REPLACE REPLACE TRIGGER TRIGGER before_order_item before_order_item BEFORE INSERT OR UPDATE BEFORE INSERT OR UPDATE OF OF quantity quantity ON ON ord_item ord_item FOR EACH ROW FOR EACH ROW declare declare v_price product.price%type; v_price product.price%type; begin begin SELECT SELECT price price INTO INTO v_price v_price FROM product FROM product WHERE WHERE product_id product_id == :new.product_id; :new.product_id; :new.total_order_item_price :new.total_order_item_price := := v_price v_price ** :new.quantity; :new.quantity; end; end;

© 2006 SkillBuilders, Inc.

This BEFORE trigger calculates the TOTAL_ORDER_ITEM_PRICE value as V_PRICE * QUANTITY and stores the result of the calculation into the ORD_ITEM table. This trigger must be a BEFORE trigger because it sets a :NEW reference variable. It also must be a ROW-level trigger so that references to :NEW are valid.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.15

10.15

Restrictions ¾ Cannot access the table that fired the trigger in the trigger logic ¾

Will get the “mutating table” error

¾ DDL is not valid in any type of trigger ¾ COMMIT, ROLLBACK, SAVEPOINT can only be used in autonomous triggers ¾ No SELECT trigger available ¾ Can never “call” a trigger directly © 2006 SkillBuilders, Inc.

Triggers cannot be fired for a SELECT statement. Triggers can never be executed explicitly like procedures or functions. COMMIT or ROLLBACK is required if the trigger is created as an autonomous transaction (more on this later in this lesson). Otherwise, these commands are not allowed in a trigger.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.16

10.16

Security Privileges ¾ One of the following privileges are necessary to create a trigger on a table: ¾ ¾ ¾ ¾ ¾

Owner of the table ALTER TABLE ALTER ANY TABLE CREATE TRIGGER CREATE ANY TRIGGER

© 2006 SkillBuilders, Inc.

You must have the appropriate privileges granted to your userid in order to CREATE, DROP, ENABLE or DISABLE a trigger. Triggers can be disabled and enabled (see the ALTER TRIGGER and ALTER TABLE statements), but not without ALTER ANY TRIGGER privilege. As long as this privilege is tightly guarded, triggers are not subject to subversion.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.17

10.17

Triggers Workshop A ¾ Row Triggers

© 2006 SkillBuilders, Inc.

Workshop – Row Triggers 1. Create a trigger called TR_MOVE_CUSTOMER_TO_HISTORY that moves a CUSTOMER row from the CUSTOMER table to the CUST_HISTORY table when the row is being deleted. Test the trigger by performing a DELETE on the CUSTOMER table and verifying that the customer(s) deleted have been moved to the CUST_HISTORY table: delete from customer where cust_no = 3 ; select * from cust_history ; Note that some deletes may trigger exceptions that we are not asking you to handle. For example, a child may exist, or some other dependency that prevents the DELETE from occurring. Ignore.

Workshop continues…

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.18

2.

Create another trigger called TR_TOTAL_ORDER_PRICE on the ORD_ITEM table. Its purpose is to maintain the TOTAL_ORDER_PRICE column of the ORD table. The TOTAL_ORDER_PRICE column in the ORD table is a derived column; it should be kept equal to the sum of all the related ITEM_PRICE * QUANTITY values in the ORD_ITEM table. Hint: Be sure to have the trigger fire if we INSERT a new order item, UPDATE an existing order item, or DELETE an order item. The calculation of the new TOTAL_ORDER_PRICE column in the ORD table should be something like the following pseudo code: If inserting: total_order_price + (:new.item_price * :new.quantity) If updating: total_order_price - (:old.item_price * :old.quantity) + (:new.item_price * :new.quantity) If deleting: total_order_price - (:old.item_price * :old.quantity)

Test the trigger by performing an INSERT, an UPDATE, and a DELETE to the ORD_ITEM table and verifying that the TOTAL_ORDER_PRICE in the ORD table is updated appropriately. .

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.19

10.19

Statement Level Trigger CREATE CREATE OR OR REPLACE REPLACE TRIGGER TRIGGER before_customer before_customer BEFORE BEFORE DELETE DELETE OR OR UPDATE UPDATE OR OR INSERT INSERT ON customer ON customer begin begin IF IF to_char(sysdate, to_char(sysdate, 'hh24') 'hh24') '18' THEN to_char(sysdate, 'hh24') > '18' THEN raise_application_error raise_application_error (-20000, (-20000, ’Data ’Data may may not not be be modified modified at at this this time!'); time!'); END END IF; IF; end; end;

© 2006 SkillBuilders, Inc.

Statement-level triggers (A.K.A. Table triggers) are executed are executed once per execution of the triggering statement; they do not have the ability to access column values. This trigger tests the time of the update against the CUSTOMER table and rejects any updates before 8AM or after 6PM. We are not using any columns from the CUSTOMER row, the variable tested is SYSDATE.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.20

10.20

Triggers Workshop B ¾ Table Triggers

© 2006 SkillBuilders, Inc.

Workshop – Table Triggers 1. Create a trigger named TR_EMP_MAINT_RESTRICT that will prevent DML on the EMPLOYEE table after business hours (i.e. only allow updates, deletes and inserts between 9 AM and 5 PM). 2. Test the trigger. If it works, change the valid hours in the trigger or change the clock (if you are on a stand-alone server) in order to verify that the trigger disallows DML during non-business hours. 3. Drop the trigger

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.21

10.21

Autonomous Transactions ¾ Autonomous transactions are called from a main transaction ¾

Main is suspended until control is returned

¾ Are fully independent from the main trans ¾ ¾ ¾

COMMIT, ROLLBACK have no affect on main Shares no locks or resources with main Beware of deadlocks

¾ Can use to log events even if main transaction rolls back ¾ Must COMMIT or ROLLBACK before return control © 2006 SkillBuilders, Inc.

Through use of an autonomous transaction, a trigger can COMMIT or ROLLBACK and not affect the triggering transaction. This is important if you want to use your trigger to log an activity regardless of whether or not the triggering (main) transaction succeeds or fails. Autonomous transactions are called from the main transaction but act fully independently from it. Note that autonomous transactions do not share resources. Consequently, if a main transaction locks a table for update and an autonomous transaction attempts to lock the same table for update, a deadlock will be reported, since the main (suspended) transaction that’s holding the lock is waiting for the autonomous transaction block to exit while the autonomous transaction is waiting for the main transaction to release the lock. In the case of the example here, the main transaction and autonomous transaction are accessing different tables so there is no potential problem for deadlock. Also note that the main transaction resumes only when you exit the autonomous transaction. If an autonomous transaction is not committed or rolled back, an error will be reported.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.22

10.22

Autonomous Trigger ¾ Use PRAGMA to define routine as autonomous ¾ Autonomous triggers must contain commit/rollback SQL> SQL> CREATE CREATE OR OR REPLACE REPLACE TRIGGER TRIGGER logit logit 22 BEFORE BEFORE INSERT INSERT ON ON emp emp 33 DECLARE DECLARE 44 PRAGMA PRAGMA AUTONOMOUS_TRANSACTION; AUTONOMOUS_TRANSACTION; 55 BEGIN BEGIN 66 INSERT INSERT INTO INTO logit logit 77 VALUES(' VALUES(' Insert Insert into into emp emp by by '||user); '||user); 88 COMMIT; COMMIT; 99 END; END; 10 10 // Trigger Trigger created. created. © 2006 SkillBuilders, Inc.

To define as autonomous, use PRAGMA AUTONOMOUS_TRANSACTION anywhere in the declarative section of any of the following: 1. schema-level (not nested) anonymous blocks 2. local, stand-alone, and packaged functions and procedures 3. methods of a SQL object type 4. database triggers Reminder that PRAGMA is a means of supplying compiler instructions. This example shows how an autonomous transaction is specified on a BEFORE INSERT database trigger. What will happen is that when an INSERT is attempted into the EMP table, the trigger will cause an autonomous transaction to be started which inserts a message into a table called LOGIT.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.23

10.23

Triggers Workshop C ¾ Autonomous Trigger

© 2006 SkillBuilders, Inc.

Workshop – Autonomous Trigger 1. Create an autonomous trigger named TR_TRANS_LOG that will insert a row into a table named LOGIT (describe the table to see that it has only one column, event). This column should be populated with a descriptive message for any DML statement that occurs on the EMPLOYEE table. 2. Enter a new employee row, update a row and delete a row to test your trigger. 3. Drop the trigger.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.24

10.24

INSTEAD OF Triggers... ¾ Facilitate updates to complex views ¾ So called, because it executes “instead of” the DML statement ¾ Given this view: CREATE CREATE VIEW VIEW manager manager AS AS SELECT e.emp_no, SELECT e.emp_no, d.dept_no, d.dept_no, d.dept_name, d.dept_name, e.lastname, e.lastname, e.firstname e.firstname FROM FROM department department d, d, employee employee ee WHERE WHERE d.mgr_no d.mgr_no == e.emp_no; e.emp_no; Continued…

© 2006 SkillBuilders, Inc.

INSTEAD OF triggers are defined to allow updates to tables via complex joins. “instead of” the actual DML operation.

They execute

The view shown above includes a join between the DEPARTMENT and EMPLOYEE table. Without the INSTEAD OF trigger, this update: UPDATE manager SET emp_no = 2 WHERE dept_no = 1; fails with: ORA-01779: cannot modify a column which maps to a non key-preserved table This means the UPDATE operation can not take place because manager and the column EMP_NO within it are not locatable/updateable because of the complex view. Oracle cannot determine how to apply this update. We discuss updateable columns in views in the lesson titled Views and Synonyms in SkillBuilders Intro to Oracle9i: SQL and SQL*Plus Workshop. In simple terms, the join complicates the view. This does not have an index to work with and it cannot find the DEPARTMENT row to update.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.25

10.25

...INSTEAD OF Triggers ¾ Support this update: UPDATE UPDATE manager manager SET SET emp_no emp_no == 22 WHERE WHERE dept_no dept_no == 1; 1;

¾ With this trigger: CREATE CREATE OR OR REPLACE REPLACE TRIGGER TRIGGER manager_update manager_update INSTEAD INSTEAD OF OF UPDATE UPDATE ON ON manager manager FOR FOR EACH EACH ROW ROW begin begin UPDATE UPDATE department department SET SET mgr_no mgr_no == :new.emp_no :new.emp_no WHERE dept_no WHERE dept_no == :new.dept_no; :new.dept_no; end; end; © 2006 SkillBuilders, Inc.

The MANAGER_UPDATE trigger intercepts the UPDATE statement and facilitates the UPDATE to the base table, DEPARTMENT. This trigger uses the trigger :NEW attribute of the manager view EMP_NO and DEPT_NO to: 1. Locate the correct department (WHERE DEPT_NO = :NEW_DEPT_NO;) 2. Update the MGR_NO column in the department table with the value in :NEW.EMP_NO

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.26

10.26

۞ Sybase / MS SQL Server vs. Oracle Triggers ¾ Sybase / MS SQL Server only has: ¾ ¾ ¾

DML triggers Statement level triggers After triggers

© 2006 SkillBuilders, Inc.

۞ Sybase and MS SQL Server’s triggers are much less sophisticated than Oracle’s triggers. 1.

Sybase / MS SQL Server only supports DML triggers. Database event triggers are not supported.

2.

Sybase / MS SQL Server has AFTER triggers but not BEFORE triggers.

3.

Sybase / MS SQL Server has STATEMENT level triggers but not row level (FOR EACH ROW) triggers. Sybase and MS SQL allow the effect of row level triggers by creating temporary insert and delete tables with all the rows affected by the trigger. But this puts the responsibility on the developer to search through these tables to do the work needed. MS SQL Server 2005 introduces the .NET trigger. This allows external .NET code rather than T-SQL code to be invoked when the action occurs. However, in both cases, it is much simpler, and thus less error prone, to use Oracle’s for each row triggers.

4.

Sybase does not support INSTEAD OF triggers but MS SQL Server 2000 does.

© 2006 SkillBuilders, Inc.

V2.1

Triggers

10.27

10.27

Triggers Workshop D ¾ INSTEAD OF Trigger

© 2006 SkillBuilders, Inc.

Workshop – INSTEAD OF Trigger 1. Create a trigger named TR_UPD_PHONE that will be an INSTEAD OF trigger. This trigger will fire when an attempt is made to update the phone column of the view PHONE_LIST. Create the view PHONE_LIST via the phone_list.sql script provided: create or replace view phone_list as select cust_no, firstname || ' ' || midinit || '. ' || lastname as name, '(' || area_code || ')' || phone as telephone# from customer; 2. The trigger should support this UPDATE: update phone_list set telephone# = '(212)555-1212' where cust_no = 1; 3. You will have to take a phone number as input, formatted (111)111-1111, and use the SUBSTR and/or INSTR functions to break it down correctly before updating the CUSTOMER table. 4. Drop the TR_UPD_PHONE trigger.

© 2006 SkillBuilders, Inc.

V2.1