Home
/
SQL

SQL Instead of DELETE trg on VIEW

Instead of DELETE trg on VIEW

USE pubs
SET NOCOUNT ON
-- drop table Table1
CREATE TABLE Table1
(a int  PRIMARY KEY,
 b datetime default getdate(),
 c varchar(10))


--drop table Table2
CREATE TABLE Table2
 (a int 
  ,message varchar(100))
GO

/* Insert 4 rows into Table1 */
INSERT INTO Table1(a) VALUES (1)
INSERT INTO Table1(a) VALUES (2)
INSERT INTO Table1(a) VALUES (3)
INSERT INTO Table1(a) VALUES (4)

/* Insert 6 rows into Table2 */
INSERT INTO Table2 VALUES (1, 'first row')
INSERT INTO Table2 VALUES (1, 'second row')
INSERT INTO Table2 VALUES (2, 'first row')
INSERT INTO Table2 VALUES (2, 'second row')
INSERT INTO Table2 VALUES (2, 'third row')
INSERT INTO Table2 VALUES (3, 'first row')
GO

/* Create a view based on a join of the tables 
   and then an INSTEAD-OF TRIGGER on the view
*/

CREATE VIEW join_view AS 
SELECT Table1.a as a1, b, c,
       Table2.a as a2, message
  FROM Table1 join Table2
   ON Table1.a = Table2.a
GO

CREATE TRIGGER DEL_JOIN
ON join_view
INSTEAD OF DELETE

AS 
  DELETE Table1
    WHERE a IN (SELECT a1 FROM deleted)
  DELETE Table2
    WHERE a IN (SELECT a2 FROM deleted)

Related Snippets