March 7, 2012 at 4:38 am
Friends,
I have a staging DB where there are around 100 History tables. These History tables are used to store History values of the updated records for the Main Tables. At present, after Trigger has been implemented in the main tables to load the history tables and inside the trigger the values from the deleted table are loaded into the Corresponding History Table. The History table has exactly the same columns as the main tables in addition to the Identity field for the History Table.
Now our team wants to eliminate the trigger and they have asked me to implement the functionality using simple queries without a Third table. So I am going to implement this functionality in 2 stages.
1. Insert a record in the history table for a newly inserted record in the main table
2. Insert a record in the history table when a record is updated in the main table.
I have generated INSERT scripts for all these tables and stored them in a Table. I am planning to execute these scripts by using the cursor which derives this insert script and executes it.
The records for the Main tables are inserted through a package using DFTs.
Please let me know if implementing cursor in this scenario would be better than using Triggers. If not, what else can be done. Your help would be appreciated.
Murali
March 7, 2012 at 5:21 am
MuraliKrishnan1980 (3/7/2012)
Friends,I have a staging DB where there are around 100 History tables. These History tables are used to store History values of the updated records for the Main Tables. At present, after Trigger has been implemented in the main tables to load the history tables and inside the trigger the values from the deleted table are loaded into the Corresponding History Table. The History table has exactly the same columns as the main tables in addition to the Identity field for the History Table.
Now our team wants to eliminate the trigger and they have asked me to implement the functionality using simple queries without a Third table. So I am going to implement this functionality in 2 stages.
1. Insert a record in the history table for a newly inserted record in the main table
2. Insert a record in the history table when a record is updated in the main table.
I have generated INSERT scripts for all these tables and stored them in a Table. I am planning to execute these scripts by using the cursor which derives this insert script and executes it.
The records for the Main tables are inserted through a package using DFTs.
Please let me know if implementing cursor in this scenario would be better than using Triggers. If not, what else can be done. Your help would be appreciated.
Murali
1. Using cursor will be much worth than using trigger.
2. What do you mean by "simple queries without a Third table"? What Third table?
3. What you have usually called "Audit triggers", they are, if implemented correctly, simple and light-weight enough...
7. Check INSERT/UPDATE with OUTPUT it may be what you are looking after. My personal choice would still be triggers!
March 7, 2012 at 5:36 am
1. Using cursor will be much worth than using trigger.
2. What do you mean by "simple queries without a Third table"? What Third table?
3. What you have usually called "Audit triggers", they are, if implemented correctly, simple and light-weight enough...
7. Check INSERT/UPDATE with OUTPUT it may be what you are looking after. My personal choice would still be triggers!
Eugene, when we use triggers we are able to retrieve the updated or deleted records from Deleted table which is a third table or a temporary table if we could say. Now they want me to load the history tables without the triggers in the main table.
We load or update records to the main table using a Data Flow Task in the package. So I believe that it would not be possible for us to use the OUTPUT clause in this scenario.
Are there any better options or is it good to continue with triggers?
March 7, 2012 at 5:44 am
What problem your team has with triggers? They don't like the name?
Loading INSERT into history will be easy without a trigger, as it will need just copy into history table whatever is inserted into the main.
With update you will get a bit more trouble, as you will need to do two steps:
1. Identify records to be updated
2. Copy them from main table into history as they are before update.
Triggers do the same work nicely behind of scene. Also, they guarantee if something is get inserted or updated by other means (hot-data-fix for example), this operation will also be audited.
March 7, 2012 at 5:54 am
Absolutely correct Eugene. I believe it would be very difficult to load the history records for update without a trigger. I have already informed my team that loading history without trigger would involve more work. But we are just checking if there would be any performance improvement by eliminating the trigger.
But our trigger does a simple insert as shown below
FOR UPDATE
AS
INSERT INTO PostingStatusListHistory
(PostingStatusID, PostingStatusCode, PostingStatusDescription, DateEntered, DateUpdated, GUID, SourceID, ExtSerialNo, Deleted)
SELECT PostingStatusID, PostingStatusCode, PostingStatusDescription, DateEntered,DateUpdated, GUID, SourceID, ExtSerialNo, Deleted
FROM DELETED
This triggers has been defined for the table PostingStatusList. I think it would be best to leave the trigger as it is as.
March 7, 2012 at 6:11 am
I also using this methode, but littile different.
1) I have History Module(contains master table, child tables.. etc, it depends on my applications)
2) I have a trigers on the table that need backup
3) When update/delete happen , prev. record be inserted into temp tables
4) At a time intervals , i takes the changes from temp tables and Update to History Module tables. This will avoids the dup. updates
March 7, 2012 at 6:30 am
MuraliKrishnan1980 (3/7/2012)
Absolutely correct Eugene. I believe it would be very difficult to load the history records for update without a trigger. I have already informed my team that loading history without trigger would involve more work. But we are just checking if there would be any performance improvement by eliminating the trigger.But our trigger does a simple insert as shown below
FOR UPDATE
AS
INSERT INTO PostingStatusListHistory
(PostingStatusID, PostingStatusCode, PostingStatusDescription, DateEntered, DateUpdated, GUID, SourceID, ExtSerialNo, Deleted)
SELECT PostingStatusID, PostingStatusCode, PostingStatusDescription, DateEntered,DateUpdated, GUID, SourceID, ExtSerialNo, Deleted
FROM DELETED
This triggers has been defined for the table PostingStatusList. I think it would be best to leave the trigger as it is as.
Inserting into PostingStatusListHistory FROM Deleted, most likely, will be faster then inserting pre-selected to-be-updated records from main "PostingStatusList".
You need just one trigger to serve INSERT, UPDATE and DELETE, and I as adviced previously it is more robast solution then to have it done in the SSIS Package, as it would server any INSERTs, UPDATEs and DELETEs.
But, it may be worth to see how you load your records into database.
Try the follwoing, you may gain performance benefits:
1. Bulk load all records into staging table which has no triggers
2. Use stored proc to load everything from staging to main table (with triggers)
3. In a stored proc, you may batch load from staging to main to minimise log utilisation.
March 7, 2012 at 7:40 am
Thanks for your input Eugene...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply