Triggers

What is a trigger? In SQL, a trigger is a sql statement or series of sql statements that are executed automatically in response to a specified event such as the update of or creation or deletion of a table or record. Each trigger must have a name that is unique to the database. Triggers are deleted when the table that they are associated with is dropped or they can be deleted with a DROP TRIGGER statement. Once created, triggers cannot be modified, to make changes the trigger must be dropped and then recreated.

DROP TRIGGER trigger_name ;

CREATE TRIGGER

This trigger automatically updates the inventory table by subtracting the Quantity of items requisitioned from the OnHandQuan value when an insert statement adds a record to the ReqDetail table.

CREATE TRIGGER inventoryupdate AFTER INSERT ON ReqDetail BEGIN
UPDATE inventory SET OnHandQuan = (OnHandQuan - NEW.Quantity) WHERE inventory.StockNumber = NEW.StockNumber;
END;

sqlite> select StockNumber,OnHandQuan,Descrip from inventory where StockNumber = 75149;
StockNumber|OnHandQuan|Descrip
75149|92|Ball Point Pens Blue Fine tip, 12pack
sqlite> CREATE TRIGGER inventoryupdate AFTER INSERT ON ReqDetail BEGIN
   ...> UPDATE inventory SET OnHandQuan = (OnHandQuan- NEW.Quantity)
   ...> WHERE inventory.StockNumber = NEW.StockNumber;
   ...> END;
sqlite> INSERT INTO ReqDetail(ReqNumber,StockNumber,Quantity,ItemCost) VALUES(1003,75149,3,0.77);
sqlite> select StockNumber,OnHandQuan,Descrip from inventory where StockNumber =75149;
StockNumber|OnHandQuan|Descrip
75149|89|Ball Point Pens Blue Fine tip, 12pack
sqlite>

Using Triggers to Enforce Referential Integrity

CREATE TRIGGER trigger_name BEFORE INSERT ON child_table BEGIN
SELECT CASE
WHEN ((SELECT parent_table . primary_key FROM parent_table WHERE parent_table . primary_key = NEW. foreign_key ) ISNULL)
THEN RAISE(ABORT, 'Error Message')
END;
END;

sqlite> CREATE TRIGGER ReqNumIn BEFORE INSERT ON ReqDetail BEGIN
   ...> SELECT CASE
   ...> WHEN ((SELECT ReqEquip.ReqNumber FROM ReqEquip WHERE ReqEquip.ReqNumber= NEW.ReqNumber) ISNULL)
   ...> THEN RAISE(ABORT, 'This Requisition number does not exist in the ReqEquip table.')
   ...> END;
   ...> END;
sqlite> insert into ReqDetail(ReqNumber,StockNumber,Quantity)values(2000,51001,15);
SQL error: This Requisition number does not exist in the ReqEquip table.
sqlite>

CREATE TRIGGER trigger_name BEFORE UPDATE ON child_table FOR EACH ROW BEGIN
SELECT CASE
WHEN ((SELECT parent_table . primary_key FROM parent_table WHERE parent_table . primary_key = NEW.foreign_key ) ISNULL)
THEN RAISE(ABORT, 'Error Message')
END;
END;

sqlite>CREATE TRIGGER ReqNumUp BEFORE UPDATE ON ReqDetail FOR EACH ROW BEGIN
   ...>SELECT CASE
   ...> WHEN ((SELECT ReqEquip.ReqNumber FROM ReqEquip
   ...>WHERE ReqEquip.ReqNumber= NEW.ReqNumber) ISNULL)
   ...> THEN RAISE(ABORT, 'update on table ReqDetail violates foreign key')
   ...> END;
   ...>END;
sqlite>

Cascading Delete

Delete records from a child table when a record from the parent table is deleted

CREATE TRIGGER trigger_name
BEFORE DELETE ON parent_table
FOR EACH ROW BEGIN
DELETE FROM child_table WHERE child_table.foreign_key = OLD. primary_key ;
END;

sqlite> CREATE TRIGGER ReqNumDel
   ...> BEFORE DELETE ON ReqEquip
   ...> FOR EACH ROW BEGIN
   ...> DELETE from ReqDetail WHERE ReqDetail.ReqNumber = OLD.ReqNumber;
   ...> END;
sqlite>

CREATE VIEW

A VIEW is a saved SELECT statement that can be used in much the same way as a table. However in SQLite a view can not be used to add, update or delete the records in the underlying tables.

CREATE VIEW view_name AS select_statement;
CREATE TEMPORARY VIEW database_name.view_name AS select_statement;

sqlite> CREATE VIEW 'ReqTotal' AS SELECT ReqEquip.ReqNumber 'Requisition',
   ...> ReqEquip.Requestor 'Requestor',ReqDate,
   ...>'$ ' || (ROUND(SUM(Quantity*ItemCost),2)) 'Req Total'
   ...> FROM ReqEquip,ReqDetail
   ...> WHERE ReqEquip.ReqNumber=ReqDetail.ReqNumber GROUP BY ReqDetail.ReqNumber;
sqlite>

sqlite> .headers on
sqlite> .mode column
sqlite> .width 10 14 10 10
sqlite> select * from ReqTotal;

Requisition  Requestor  ReqDate   Req Total 
-----------  --------------  ----------  ---------- 
1000  Carl Jones   2007/10/30  $ 24.12 
1001  Peter Smith  2007/11/05  $ 13.51 
1002  Carl Jones  2007/11/06   $ 10.57 
1003  Mike Smith  2007/12/01  $ 2.31 
1004  Steve North   2007/12/02  $ 19.4 
1005  Harold Allen  2007/12/04  $ 54.91   

However a trigger assigned to a view can be used to insert,update or delete records in underlying tables of the view.

Trigger to insert records into the underlying tables of a view

CREATE TRIGGER insert_view
INSTEAD OF INSERT ON view_name
FOR EACH ROW BEGIN
INSERT INTO table_one(field_1,field_2,field_3)
VALUES(NEW.field_1,NEW.field_2,NEW.field_3);
INSERT INTO table_two(field_4,field_5,field_6)
VALUES(NEW.field_4,field_5,field_6);
END;

Trigger to update records in the underlying tables of a view

CREATE TRIGGER update_view
INSTEAD OF UPDATE ON view_name FOR EACH ROW BEGIN UPDATE table_one
SET field_1= new.field_1
field_2 = new.field_2
field_3 = new.field_3
WHERE key_id = OLD.key_id ;
UPDATE table_two
SET field_4 = new.field_4
field_5 = new.field_5
field_6 = new.field_6
WHERE key_id = OLD.key_id;
END;


Colin Riley -- December 2007 --