?
Solved

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

Posted on 2012-12-25
9
Medium Priority
?
551 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
8 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 1000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

755 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