Solved

trigger or Transaction

Posted on 2009-07-04
5
206 Views
Last Modified: 2012-05-07
I need to save data to a history table whenever data is entered into one table. Shall I use Transaction to make sure data always goes to history table in the same stored procedure that inserts data to the original table, or is it better to use trigger in the original table to write data to history table? Any ideas would be appreciated.
0
Comment
Question by:suecnus
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24778452
Dont use trigger, Just insert the data in  the history table, about the use of transaction, i dont prefer using a transaction just for History purpose unless it is very worthy
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 24778468
If you are in control of the application, then a trigger would normally seem to be overkill.

I use triggers when I don't have access to change the source of the application (shrink wrapped apps - accounting, payroll, t&a, etc.).

Then the trigger is the only route.

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24778718
You are looking for Audit trail and for that purpose I would go for trigger, herewith I have written one small example at my blog, have a look:

http://www.sqlhub.com/2009/03/auditing-trail-with-trigger-in-sql.html
0
 

Author Comment

by:suecnus
ID: 24921088
"I use triggers when I don't have access to change the source of the application". Can you explain it in a bit more detail? I can either use trigger in the transaction table to insure data always inserted to the history table before it is inserted to transaction table. Or I can use transaction to insert data to transaction table and history at the same time. Does whether I have access to application source make a difference? Thanks.
0
 
LVL 40

Accepted Solution

by:
RQuadling earned 500 total points
ID: 24922951
I have "shrink-wrapped" Windows applications linked to an SQL server. The application source is unmodifiable. So I cannot alter the statements it uses. It is NOT using SPs for interacting with the DB.

So, if I want to introduce a new column and have it maintained, I need to do this within a trigger.

For example:

We have a purchase order system. By careful use of triggers, I was able to convert the purchase ledger from a straight supplier table into a supplier by contract table. Each supplier record is for a single contract.

Each contract runs to a different financial calendar.

Triggers are being used to maintain the current period for the purchase orders raised.

The application has not been amended. It still performs all the functions we require of it, but the triggers have allowed us to provide a significant addition. The extra information is maintained via a PHP based intranet web-app.

Of course, a trigger has to be a little more careful. You cannot break the main transaction. In our case, the shrink-wrapped app is not as tolerant of rollbacks as I would like, but I know this and therefore am very careful in the triggers - a LOT of error checking and logs to an email alerter when there is an issue which cannot be solved inline.

Ultimately, which to use comes under the heading of "depends".

For something like an auto-archive system, then maybe triggers are simply easier to work with, like RiteshShah suggested. You can normally create a single SP and attach it to the tables you want to audit.

I'm pretty sure with some clever coding, you can pass the "inserted" and "deleted" tables to an SP for examination, along with the name of the table. I think using XML is probably going to be the best bet to pass the inserted/deleted tables without having to hardcode every column name ...

Something like this ...

CREATE TRIGGER dbo.UsersAudit
   ON  dbo.Users
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
      SET NOCOUNT ON;

      DECLARE
            @xml_Inserted XML,
            @xml_Deleted XML
      SELECT
            @xml_Inserted = (SELECT * FROM Inserted FOR XML),
            @xml_Deleted  = (SELECT * FROM Deleted  FOR XML)
      EXEC spAuditTrail 'Users', @xml_Inserted, @xml_Deleted
END


For the spAuditTrail SP (proof only) ...

CREATE PROCEDURE spAuditTrail
      @s_TableName varchar(50),
      @xml_Inserted xml,
      @xml_Deleted xml
AS
BEGIN
      SET NOCOUNT ON;

      SELECT @xml_Inserted, @xml_Deleted
END


When I insert a row into the Users table ...

INSERT INTO USERS VALUES('Simon', 'Simon@bob.com', 'a', null,null,null,null,0,1,1,1)

I get a result set from the SP of 2 columns. Column 1 contains ...

<Inserted UniqueID="25" Username="Simon" Email="Simon@bob.com" PasswordHash="a" PermissionDisabled="0" PermissionUserAdmin="1" PermissionReportAdmin="1" PermissionRunReports="1" />

Column 2 is null.


I then inserted 9 more "Simonx" records.

Then I updated all the odd simons...

Now both columns have some output from the SP.

Column1 ...

<Inserted UniqueID="35" Username="Simon9Odd" Email="Simon9@bob.com" PasswordHash="a" PermissionDisabled="0" PermissionUserAdmin="1" PermissionReportAdmin="1" PermissionRunReports="1" />
<Inserted UniqueID="33" Username="Simon7Odd" Email="Simon7@bob.com" PasswordHash="a" PermissionDisabled="0" PermissionUserAdmin="1" PermissionReportAdmin="1" PermissionRunReports="1" />
<Inserted UniqueID="31" Username="Simon5Odd" Email="Simon5@bob.com" PasswordHash="a" PermissionDisabled="0" PermissionUserAdmin="1" PermissionReportAdmin="1" PermissionRunReports="1" />
<Inserted UniqueID="29" Username="Simon3Odd" Email="Simon3@bob.com" PasswordHash="a" PermissionDisabled="0" PermissionUserAdmin="1" PermissionReportAdmin="1" PermissionRunReports="1" />
<Inserted UniqueID="27" Username="Simon1Odd" Email="Simon1@bob.com" PasswordHash="a" PermissionDisabled="0" PermissionUserAdmin="1" PermissionReportAdmin="1" PermissionRunReports="1" />

Column2 ...

<Deleted UniqueID="35" Username="Simon9" Email="Simon9@bob.com" PasswordHash="a" PermissionDisabled="0" PermissionUserAdmin="1" PermissionReportAdmin="1" PermissionRunReports="1" />
<Deleted UniqueID="33" Username="Simon7" Email="Simon7@bob.com" PasswordHash="a" PermissionDisabled="0" PermissionUserAdmin="1" PermissionReportAdmin="1" PermissionRunReports="1" />
<Deleted UniqueID="31" Username="Simon5" Email="Simon5@bob.com" PasswordHash="a" PermissionDisabled="0" PermissionUserAdmin="1" PermissionReportAdmin="1" PermissionRunReports="1" />
<Deleted UniqueID="29" Username="Simon3" Email="Simon3@bob.com" PasswordHash="a" PermissionDisabled="0" PermissionUserAdmin="1" PermissionReportAdmin="1" PermissionRunReports="1" />
<Deleted UniqueID="27" Username="Simon1" Email="Simon1@bob.com" PasswordHash="a" PermissionDisabled="0" PermissionUserAdmin="1" PermissionReportAdmin="1" PermissionRunReports="1" />


So, that has now got the before and after rows for any table into a single SP with no need to manually name every column. You add a new column to the table, it will automatically be sent to the SP.

So, the next part is processing the XML data.

At the moment, I don't know SQL's XML manipulation very well.

But I wouldn't process the audit trail live.

Store the xml in a table (tblPreAuditTrail) and have an external process trawl through the table to update the actual AuditTrail table.

The main advantage here is that the audit process will be holding up the main request. For every single change to the table. You may end up spending a LOT of time in there, especially for larger tables or sets of changes (if you update 1000 rows of a 50 column table, thats 50,000 values to compare - a LOT of processing!).

So, simply log the befores and afters.

Now, you can schedule the processing of the befores/afters sometime later, maybe out of hours or in a known downtime, and you could use something a little easier to work with (say an XML comparitor). My language is PHP and it wouldn't be too difficult to find a set of changes in 2 xml documents by node/attribute.

Obviously, if your audit is significantly important and live is needed, then live it will need to be, but maybe hiving that off to a fast external process could be the answer there also (outside of my knowledge at the moment).

How does that sound?
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

910 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

23 Experts available now in Live!

Get 1:1 Help Now