SQL Server/T-SQL Tutorial/Trigger/Trigger on view

CREATE INSTEAD OF DELETE TRIGGER ON a view

   <source lang="sql">

1> 2> SET NOCOUNT ON 3> 4> CREATE TABLE Table1 5> (a int PRIMARY KEY, 6> b datetime default getdate(), 7> c varchar(10)) 8> 9> 10> CREATE TABLE Table2 11> (a int 12> ,message varchar(100)) 13> GO 1> 2> INSERT INTO Table1(a) VALUES (1) 3> INSERT INTO Table1(a) VALUES (2) 4> INSERT INTO Table1(a) VALUES (3) 5> INSERT INTO Table1(a) VALUES (4) 6> 7> INSERT INTO Table2 VALUES (1, "first row") 8> INSERT INTO Table2 VALUES (1, "second row") 9> INSERT INTO Table2 VALUES (2, "first row") 10> INSERT INTO Table2 VALUES (2, "second row") 11> INSERT INTO Table2 VALUES (2, "third row") 12> INSERT INTO Table2 VALUES (3, "first row") 13> GO Msg 2627, Level 14, State 1, Server J\SQLEXPRESS, Line 2 Violation of PRIMARY KEY constraint "PK__Table1__686944BF". Cannot insert duplicate key in object "dbo.Table1". The statement has been terminated. Msg 2627, Level 14, State 1, Server J\SQLEXPRESS, Line 3 Violation of PRIMARY KEY constraint "PK__Table1__686944BF". Cannot insert duplicate key in object "dbo.Table1". The statement has been terminated. Msg 2627, Level 14, State 1, Server J\SQLEXPRESS, Line 4 Violation of PRIMARY KEY constraint "PK__Table1__686944BF". Cannot insert duplicate key in object "dbo.Table1". The statement has been terminated. Msg 2627, Level 14, State 1, Server J\SQLEXPRESS, Line 5 Violation of PRIMARY KEY constraint "PK__Table1__686944BF". Cannot insert duplicate key in object "dbo.Table1". The statement has been terminated. 1> 2> CREATE VIEW join_view AS 3> SELECT Table1.a as a1, b, c, 4> Table2.a as a2, message 5> FROM Table1 join Table2 6> ON Table1.a = Table2.a 7> GO 1> 2> CREATE TRIGGER DEL_JOIN 3> ON join_view 4> INSTEAD OF DELETE 5> 6> AS 7> DELETE Table1 8> WHERE a IN (SELECT a1 FROM deleted) 9> DELETE Table2 10> WHERE a IN (SELECT a2 FROM deleted) 11> GO 1> 2> drop TRIGGER DEL_JOIN; 3> drop VIEW join_view; 4></source>

INSTEAD OF INSERT on a view

   <source lang="sql">

6> CREATE TABLE orders 7> (orderid INT NOT NULL, 8> price MONEY NOT NULL, 9> quantity INT NOT NULL, 10> orderdate DATETIME NOT NULL, 11> total AS price * quantity, 12> shippeddate AS DATEADD (DAY, 7, orderdate)) 13> GO 1> 2> CREATE VIEW all_orders 3> AS SELECT orderid, price, quantity, orderdate, total, shippeddate 4> FROM orders 5> GO 1> 2> CREATE TRIGGER tr_orders ON all_orders INSTEAD OF INSERT 3> AS BEGIN 4> INSERT INTO orders SELECT orderid, price, quantity, orderdate FROM inserted 5> END 6> GO 1> 2> drop trigger tr_orders; 3> GO 1> 2> drop view all_orders; 3> GO 1> 2> drop table orders;</source>

INSTEAD OF UPDATE trigger on a view

   <source lang="sql">

2> 3> 4> CREATE TABLE Customers ( 5> CustomerID nchar (5) NOT NULL , 6> CompanyName nvarchar (40) NOT NULL , 7> ContactName nvarchar (30) NULL , 8> ContactTitle nvarchar (30) NULL , 9> Address nvarchar (60) NULL , 10> City nvarchar (15) NULL , 11> Region nvarchar (15) NULL , 12> PostalCode nvarchar (10) NULL , 13> Country nvarchar (15) NULL , 14> Phone nvarchar (24) NULL , 15> Fax nvarchar (24) NULL 16> ) 17> GO 1> 2> INSERT Customers VALUES("1","A","Maria", "Sales", "Str. 57", "Berlin" ,NULL,"12209", "Germany","111-1111111","111-1111111") 3> INSERT Customers VALUES("2","M","Joe", "Owner", "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL) 4> INSERT Customers VALUES("3","H","Thomas", "Sales", "Sq. 111","London" ,NULL,"1D00P", "UK", "(444) 444-4444","(444) 444-4444") 5> INSERT Customers VALUES("4","B","Berg", "Order", "Blv 8","Toronto" ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55") 6> INSERT Customers VALUES("5","S","Moos", "Sales", "Fort 57","New York" ,NULL,"68306", "Germany","6666-66666","6666-77777") 7> INSERT Customers VALUES("6","F","Cite", "Manager","24 ","Dalles" ,NULL,"67000", "France", "88.60.15.31","88.60.15.32") 8> INSERT Customers VALUES("7","C","Sommer", "Owner", "Araq, 67","Paris" ,NULL,"28023", "Spain", "(91) 555 22 82","(91) 555 91 99") 9> INSERT Customers VALUES("8","P","Leb", "Owner", "12 ","Beijing" ,NULL,"13008", "France", "91.24.45.40","91.24.45.41") 10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745") 11> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE VIEW CustomerOrders_vw 3> WITH SCHEMABINDING 4> AS 5> SELECT cu.rupanyName 6> FROM dbo.Customers AS cu 7> GO 1> 2> 3> 4> 5> create TRIGGER trCustomerOrderUpdate ON CustomerOrders_vw 6> INSTEAD OF UPDATE 7> AS 8> BEGIN 9> IF (SELECT COUNT(*) FROM Inserted) > 0 10> BEGIN 11> IF (COLUMNS_UPDATED() | 96) != 96 12> BEGIN 13> RAISERROR("Updates only supported for UnitPrice and Quantity", 16,1) 14> END 15> ELSE BEGIN 16> RAISERROR("again", 16,1) 17> 18> END 19> END 20> END 21> GO 1> drop TRIGGER trCustomerOrderUpdate; 2> drop view CustomerOrders_vw; 3> drop table Customers; 4> GO</source>