SQL Server/T-SQL Tutorial/Trigger/Trigger on view
CREATE INSTEAD OF DELETE TRIGGER ON a view
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>
INSTEAD OF INSERT on a view
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;
INSTEAD OF UPDATE trigger on a view
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