CODEWITHSUNDEEP
sqltriggersinsertsql-update
Manoj Singh
Manoj Singh

Reputation: 7707

How to create SQL trigger for insert for three tables

I want to create SQL Trigger as below:

CREATE TRIGGER Multi_Insert ON ITEMS, COMPONENT, LINK_INFO
FOR INSERT  
AS  
INSERT INTO Multi_Insert (ACTION, PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID)  
SELECT 'ADD', PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID  
FROM inserted

In above trigger earlier all the details where coming from one table ITEMS, now some values has been moved to other tables like SCHEMA_ID is now in COMPONENT and URL in LINK_INFO.

Actually on the insert/update/delete of above tables, we will be updating, inserting and deleting the Multi_Insert table, now we can write three trigger for insert event on every table insert ITEMS, COMPONENT, LINK_INFO, however I want to achieve this using one trigger only

Is that is possible?

Upvotes: 3

Views: 2177

Answers (1)

Ravi
Ravi

Reputation: 31437

DECLARE @i INT, @d INT;
SELECT @i = COUNT(*) FROM inserted;
SELECT @d = COUNT(*) FROM deleted;
IF @i + @d > 0
BEGIN
    IF @i > 0 AND @d = 0
        -- insert
    IF @i > 0 AND @d > 0
        -- update
    IF @i = 0 AND @d > 0
        -- delete
END

As i told you, i'm not so much clear about your question. And, whatsoever, i got from you question. I wrote simple query, so that you can take the reference and use it accordingly.

From the above query, @d and @i two variable declared and from the 2nd and 3rd line i'm storing the count (number of rows) from different tables. And, then i'm checking the value whether tables have some row or not. Then, performing the insert, update and delete after checking whether tables have some row or not. Now, what was the difficult part in that ?? I gave you, how you can perform all three in single trigger rest is up to you, how you can use them. You may also refer this link

Upvotes: 1

Related Questions