Solved

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

Posted on 2012-12-25
9
533 Views
Last Modified: 2013-01-15
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.
0
Comment
Question by:tia_kamakshi
  • 4
  • 3
9 Comments
 
LVL 25

Expert Comment

by:TempDBA
ID: 38720100
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..
0
 

Author Comment

by:tia_kamakshi
ID: 38720149
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
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38728635
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.
0
 

Author Comment

by:tia_kamakshi
ID: 38730319
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 38730412
I am suggesting you DO NOT populate the AUTN_ITEMS table with data that has been moved from ITEMS into PAGES and COMPONENTS.

When dealing with triggers and mulitple tables, you need to be very very sure of the isolation levels in play and the locking strategies. You could end up creating a nightmare.

If you are extremely confident of how and when and the sequence of locks within a transaction (or even if all three tables are contained within a transaction), then you might consider it.

Then the INSTEAD OF triggers will not work for CHANGE or DELETE if you have referential integrity (foreign keys) and cascading deletes or updates.

But there is no real point and no real need to do that if you dont have to.

I guess the real question is "do you have to"

Most of that question will depend on what you are using the AUTN table for.

Looking at how the current triggers work, there will be a whole pile of inserts and updates but there is no real chronology that we can see. Chronology is vitally important especially if the last thing is a delete (ie the row no longer exists).

So... If for an AUDIT trail, have to guess that there is a date time of update (with a default value) and/or an identity to help uniqueness and an indication of chronology.

Or... if for a staging table for maybe an ETL type process, then have to guess that we will be using the AUTN to help transfer data - and reset (truncated). Even still, a date time or an identity is not unreasonable.

Now... I am assuming you are talking about TRIDION the CMS system, and have assumed (given the TA's) that you have implemented over the top of MS SQL Server. But, tridion can also use Oracle, or DB2...

So, lets sit back for a minute and discuss a couple of things...

1) What do you use the AUTN table for ?
2) Are you using MS SQL Server as your back end - what version - or what DB ?
3) Is there a specific chronology you are using in your AUTN table ?

If you can answer those couple of questions, we should be able to help a lot more...
0
 

Author Comment

by:tia_kamakshi
ID: 38738897
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
0
 

Author Closing Comment

by:tia_kamakshi
ID: 38777081
Did not get any working scenario
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38777805
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...

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 33
Would coalesce be what I use here and, if so, how? 8 42
Mssql SQL query 14 28
SqlAdvisor 2016 3 11
Creating and Managing Databases with phpMyAdmin in cPanel.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now