CODEWITHSUNDEEP
sql-server
envyM6
envyM6

Reputation: 1237

Trigger to update multiple tables

trying to create a trigger which should insert data into three tables upon insertion of its parent table.

Diagram

enter image description here

Table_1

enter image description here

Table_2

enter image description here

Table_3

enter image description here

Table_4

enter image description here

And my object explorer

enter image description here

The code block I'm trying to use is 

USE [demo1]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[autoupdate]
    ON [dbo].[Table_1]
    AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.Table_2(ONEID) 
        SELECT ONEID FROM INSERTED i
        WHERE NOT EXISTS (SELECT 1 FROM dbo.Table_2 t2 WHERE t2.ONEID = i.ONEID) 
        AND i.ONEID IS NOT NULL;

END

GO

It works and updates the Table_2 with the data taken from ONEID. Now How can I modify this to insert ONEID to rest of the tables?

For Dave

INSERT INTO dbo.Table_2(ONEID)
SELECT ONEID FROM INSERTED i 
WHERE NOT EXISTS (SELECT 1 FROM dbo.Table_2 t2 WHERE t2.ONEID = i.ONEID) 
AND i.ONEID IS NOT NULL;

INSERT INTO dbo.Table_3(ONEID)
SELECT ONEID FROM INSERTED i 
WHERE NOT EXISTS (SELECT 1 FROM dbo.Table_2 t2 WHERE t2.ONEID = i.ONEID)
AND i.ONEID IS NOT NULL;

INSERT INTO dbo.Table_4(ONEID)
SELECT ONEID FROM INSERTED i 
WHERE NOT EXISTS (SELECT 1 FROM dbo.Table_2 t2 WHERE t2.ONEID = i.ONEID) 
AND i.ONEID IS NOT NULL;

Upvotes: 0

Views: 29

Answers (1)

Dave.Gugg
Dave.Gugg

Reputation: 6771

Like this:

INSERT INTO dbo.Table_2(ONEID)
SELECT ONEID FROM INSERTED i 
WHERE NOT EXISTS (SELECT 1 FROM dbo.Table_2 t2 WHERE t2.ONEID = i.ONEID) 
AND i.ONEID IS NOT NULL;

INSERT INTO dbo.Table_3(ONEID)
SELECT ONEID FROM INSERTED i 
WHERE NOT EXISTS (SELECT 1 FROM dbo.Table_3 t3 WHERE t3.ONEID = i.ONEID)
AND i.ONEID IS NOT NULL;

INSERT INTO dbo.Table_4(ONEID)
SELECT ONEID FROM INSERTED i 
WHERE NOT EXISTS (SELECT 1 FROM dbo.Table_4 t4 WHERE t4.ONEID = i.ONEID) 
AND i.ONEID IS NOT NULL;

Upvotes: 1

Related Questions