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 -- Alphabetical Subject Index -- Contact form