February 22, 2011 at 12:38 pm
i have the following trigger.
basically, anytime i update a table, i need to copy the row to a history table and then do the update on the main table....
TRIGGER [dbo].[trg_SourceHistory]
ON [dbo].[tblSource]
for UPDATE
AS
INSERT INTO tblHistorySource
select *, getdate()
from updated
BEGIN
RAISERROR('Error in Source Hisotry Trigger' ,16,1)
ROLLBACK TRAN
END
but i'm getting an error in the trigger when trying to execute an update on that table, so no changes are saved. please ehlp
February 22, 2011 at 12:41 pm
tkacyndra (2/22/2011)
i have the following trigger.basically, anytime i update a table, i need to copy the row to a history table and then do the update on the main table....
TRIGGER [dbo].[trg_SourceHistory]
ON [dbo].[tblSource]
for UPDATE
AS
INSERT INTO tblHistorySource
select *, getdate()
from UPDATED
BEGIN
RAISERROR('Error in Source Hisotry Trigger' ,16,1)
ROLLBACK TRAN
END
but i'm getting an error in the trigger when trying to execute an update on that table, so no changes are saved. please ehlp
THE ERROR IS: Invalid object name 'UPDATED'.
You want INSERTED... if you want the new changes. You want DELETED if you want to store the historical information. UPDATED doesn't exist.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 22, 2011 at 12:42 pm
when i change it to INSERTED, it still does not go though... is there a way to debug it somehow to see where the error is? or to maybe get more details.
the exception i get thrown with INSERTED is: Message"Error in Source Hisotry Trigger The transaction ended in the trigger. The batch has been aborted."String
February 22, 2011 at 12:47 pm
you might need to explicitly define the columns:
INSERT INTO tblHistorySource(Column1,ColumnList,CreationDate)
select Column1,ColumnList, getdate()
from DELETED --the old values...new values are ijnt eht able if you need to compare them.
also, completely remove this code: this isn't stopping due to an error...it's just rolling back your trigger.
BEGIN
RAISERROR('Error in Source Hisotry Trigger' ,16,1)
ROLLBACK TRAN
END
Lowell
February 22, 2011 at 12:49 pm
tkacyndra (2/22/2011)
when i change it to INSERTED, it still does not go though... is there a way to debug it somehow to see where the error is? or to maybe get more details.the exception i get thrown with INSERTED is: Message"Error in Source Hisotry Trigger The transaction ended in the trigger. The batch has been aborted."String
Well, I'd assumed this wasn't the entire trigger code:
CREATE TRIGGER [dbo].[trg_SourceHistory]
ON [dbo].[tblSource]
for UPDATE
AS
INSERT INTO tblHistorySource
select *, getdate()
from [INSERTED]
BEGIN
RAISERROR('Error in Source Hisotry Trigger' ,16,1)
ROLLBACK TRAN
END
If it is, you're ALWAYS going to Error. You're not doing any TRY/CATCH or @@ERROR checks.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 22, 2011 at 12:50 pm
do i then have to list ALL values, or can i just list the ones i want to keep track of? how would it know where to put them?
February 22, 2011 at 12:53 pm
Craig,
Thank you,
i think i got it 🙂
/****** Object: Trigger [dbo].[trg_SourceHistory] Script Date: 02/22/2011 14:54:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_SourceHistory]
ON [dbo].[tblSource]
for UPDATE
AS
begin try
INSERT INTO tblHistorySource
select *, getdate()
from [DELETED]
end try
begin catch
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
RAISERROR('Error in Source Hisotry Trigger' ,16,1)
ROLLBACK TRAN
END CATCH
go
this seems to work, but please let me know if there's anything i missed taht could make it better 🙂
February 22, 2011 at 1:55 pm
Seems straightforward enough. Since you're working from the historical entry point of view, rather then all items, in your audit, two things.
First, modify this query to be used for UPDATE, DELETE.
Second, realize that if you ever want to see the full history of a row, you will have to UNION ALL your audit table with your main table.
Usually when I build audit tables like this, especially if the data is very transient (every row is modified at least once), I'll do an INSERT, UPDATE trigger off INSERTED, instead of UPDATE, DELETE off DELETED.
I would only usually use a delete mechanism if I expected my audit table to stay at ~30% or less of the main table, or the data is incredibly wide. Otherwise, the unions later just aren't worth the price.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 22, 2011 at 2:09 pm
Thank you!
February 22, 2011 at 3:41 pm
Just a comment that will save you untold hours of pulling your hair out in a few months when the columns in tblSource get modified...
INSERT INTO tblHistorySource
select *, getdate()
Don't use select *. You should list all the columns you want to select. There will be a column added to this table at some point (likely in the near future) and all of a sudden your columns don't match anymore and it will bomb. Then you will spend several hours trying to figure out what this failed and kick yourself silly for not just listing the columns you want now. 😎
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 22, 2011 at 3:54 pm
Sean Lange (2/22/2011)
Just a comment that will save you untold hours of pulling your hair out in a few months when the columns in tblSource get modified...
INSERT INTO tblHistorySource
select *, getdate()
Don't use select *. You should list all the columns you want to select. There will be a column added to this table at some point (likely in the near future) and all of a sudden your columns don't match anymore and it will bomb. Then you will spend several hours trying to figure out what this failed and kick yourself silly for not just listing the columns you want now. 😎
Actually, my personal favorite for making sure I *do* remember to alter my audit tables as well. Guess it takes all kinds. I like the SELECT * personally as a safety catch, for audit triggers anyway.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 23, 2011 at 7:16 am
Craig Farrell (2/22/2011)
Sean Lange (2/22/2011)
Just a comment that will save you untold hours of pulling your hair out in a few months when the columns in tblSource get modified...
INSERT INTO tblHistorySource
select *, getdate()
Don't use select *. You should list all the columns you want to select. There will be a column added to this table at some point (likely in the near future) and all of a sudden your columns don't match anymore and it will bomb. Then you will spend several hours trying to figure out what this failed and kick yourself silly for not just listing the columns you want now. 😎
Actually, my personal favorite for making sure I *do* remember to alter my audit tables as well. Guess it takes all kinds. I like the SELECT * personally as a safety catch, for audit triggers anyway.
Certainly something to be said for that. I can remember when I was pretty new to databases how frustrating it was sometimes to find errors in triggers for just that sort of scenario. I guess the upside is that the OP is throwing an exception that would steer them to the trigger pretty quickly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply