Solved

trigger or Transaction

Posted on 2009-07-04
5
212 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:Richard Quadling
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:
Richard Quadling 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

615 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