Avatar of tia_kamakshi
tia_kamakshi
Flag for United Arab Emirates asked on

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

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

Open in new window


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.
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Mark Wills

8/22/2022 - Mon
TempDBA

Here you can go with two ways:-
1. Insert the values in AUTN_INSERT when something new gets added to Items table with the trigger leaving both the columns blank and schedule a job that runs periodically and update the value if some things gets added to the Pages and Components table.
2. Or else, since other tables i.e. Pages and Components looks like child table and the Item_Reference_ID is primary key in the Items table. If so, then you need to run your triggers on all the three tables. BTW, if you are going with normalizing the actual table, you can also thing about normalizing similarly your audit table too..
tia_kamakshi

ASKER
Hi Expert,

I would like to go with second option

2. Or else, since other tables i.e. Pages and Components looks like child table and the Item_Reference_ID is primary key in the Items table. If so, then you need to run your triggers on all the three tables. BTW, if you are going with normalizing the actual table, you can also thing about normalizing similarly your audit table too.

Can you please provide me sample code for the same as well as it would well optimized

Also,
can we create a View on these tables with JOIN's and then define INSTEAD OF trigger on this view. But this demand perform action on view, not on base tables

What could be performance impact on this

thanks
Mark Wills

Dont go down the slippery slope of INSTEAD OF triggers.

Simply create triggers on the other two tables and maintain their own version of the AUTN_PAGES and AUTN_COMPONENTS the same way you are doing the triggers for ITEM into AUTN_ITEMS

You can always create a view over the three tables to make the data appear as it always has done.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
tia_kamakshi

ASKER
Hi Expert,

Thanks for your valuable suggestion on creating triggers, can you please describe it more about the performance difference between both the implementation.

1) Creating triggers on the other two tables
2) Creating a View using three tables and then later on using Trigger to populate my AUTN_ITEMS from that.

My concerns is really on the performance.

Thanks.
ASKER CERTIFIED SOLUTION
Mark Wills

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
tia_kamakshi

ASKER
Hi Mark,

Happy New Year and thanks for detail description.

1) What do you use the AUTN table for ?
Answer: We have autonomy search implmented on our website, we use this table are pointer and do the indexing on the basis of this. Once indexing is done we update the "Flag" field with value "2" so that while checking next time it always pics the latest records.
2) Are you using MS SQL Server as your back end - what version - or what DB ?
Answer: We are using MS SQL server 2008.
3) Is there a specific chronology you are using in your AUTN table ?
Answer: Not such, whenever new page gets inserted a new record get added using "ADD", for update "UPD" and when any page is deleted it adds "DEL" in it. Records are added/updated/deleted using publishing modeling, so when user unpublish any page logically it get deleted from the server and so we add "DEL" in it.

Please suggest some good ways to handle this condition as performance it real constraint
tia_kamakshi

ASKER
Did not get any working scenario
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mark Wills

Ouch, Grade "C" and all because I didnt see your last post (either that, or we really were not much help as is implied by "C" grade)...

Couple of things...

1) The previous posts do have quite a bit in it in terms of a working scenario and the challenge with triggers.

2) Doing indexing, then flagging as 2 so that new rows are identified (as in not being flagged) means you will end up with a lot of redundant data, but, it is simply a log so rows are (essentially) only ever added.

3) your "change" doesnt show the original row, so it really should post the Deleted as well as the Inserted entries otherwise you will might skew results because of the retained original inserted.

4) However you are flagging (with a 2), you will also need to flag the additional entries due to URL or Schema_ID changes - assuming they are critical to the reindexing process.

5) Now we finally know for sure what database you are using, we could created the triggers etc for you, but part of the solution is trying to help understand (both ways).

But then this is only a "C" grade answer, so guess we dont have to get into it much more...

https://www.experts-exchange.com/help/viewHelpPage.jsp?helpPageID=26