troubleshooting Question

How to get data from three tables in SQL insert/update/delete Trigger

Avatar of tia_kamakshi
tia_kamakshiFlag for United Arab Emirates asked on
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2008SQL
8 Comments1 Solution579 ViewsLast Modified:
Hi Experts,

I was having SQLtrigger earlier in tridion 2009 database, now after the tridion 2011 database upgrade there are some changes in our database.

Old Code:

CREATE TRIGGER AUTN_INSERT ON [ITEMS]  
FOR INSERT  
AS  
    INSERT INTO AUTN_ITEMS (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  

GO
CREATE TRIGGER AUTN_UPDATE ON [ITEMS]  
FOR UPDATE  
AS  
    INSERT INTO AUTN_ITEMS (ACTION, PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID)  
        SELECT 'UPD', PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID  
        FROM inserted  
GO

CREATE TRIGGER AUTN_DELETE ON [ITEMS]  
FOR DELETE  
AS  
    INSERT INTO AUTN_ITEMS (ACTION, PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID)  
        SELECT 'DEL', PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID  
        FROM deleted  
Go

Now if you see above SQL code all the data where in ITEMS table and everything was easily picked up using above Triggers.

Let take an example of INSERT Trigger and if fixed for insert it will work for update and delete as well.

CREATE TRIGGER AUTN_INSERT ON [ITEMS]  
FOR INSERT  
AS  
    INSERT INTO AUTN_ITEMS (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  

GO

In above code "URL" and "SCHEMA_ID" has been removed from ITEMS tables, now URL is moved to another table called "PAGES" and SCHEMA_ID is moved to another table called "COMPONENTS" so in above trigger code instead of taking values from one table (ITEMS) we need to take values from three tables (ITEMS, PAGES and COMPONENTS).

It linking between these table is shown below:

ITEMS Table:
ITEM_REFERENCE_ID
ITEM_TYPE
..
..

PAGES Table:
ITEM_REFERENCE_ID
URL
..

COMPONENTS Table:

ITEM_REFERENCE_ID
SCHEMA_ID
..

On the basis of above table changes I want to update my above SQL Trigger code.

Please suggest how we can do the changes

Thanks.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros