Solved

trigger or Transaction

Posted on 2009-07-04
5
204 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

19 Experts available now in Live!

Get 1:1 Help Now