asked
Create a Trigger to record changes to a database table
I have an MSSQL database table that is populated from a web form. I want to be able log who made changes to each record and when the changes were made. I have read as much as I can to try and figure this out myself, but I just keep getting error messages so I need some help. My original table is called 'faults'. This has all the fields necessary to log the details of the fault call. I have created another table called 'faultsupdated' which only contains some of the fields that I need to log the changes (these are: faultid, SalesOrderNo, [fault-name], lastedit and lastedittechnician). This is the sql I have written so far (limited by my understanding) CREATE TRIGGER [dbo].[NewTrigger] ON [dbo].[faults] AFTER UPDATE AS BEGIN IF Exists(Select * From faults) BEGIN Insert Into fault_updates ( faultid, SalesOrderNo, [fault-name], lastedit, lastedittechnician Action ) Select faultid, SalesOrderNo, [fault-name], lastedit, lastedittechnician 'Update' From inserted END GO When I try to save this I get the following error: 42000 - [SQL Server]Incorrect syntax near 'Action' 42000 - [SQL Server]Incorrect syntax near 'END' As I said at the top, I really need a way of tracking changes to the original 'faults' database where I can see which user changed it and when they changed it. I have no preference either way regarding Triggers (I only just became familiar with the term) so any help would be greatly appreciated to achieve my goal. Thanks
Perhaps a result of pasting into this site, but I notice you have two BEGIN statements and only one END statement? Also - I think you are missing a comma before the field "Action" in your field list in the INSERT.
Thanks for helping out, this is the code that worked for me in the end. CREATE TRIGGER [dbo].[NewTrigger] ON [dbo].[faults] AFTER UPDATE AS BEGIN IF Exists(Select * From faults) Insert Into fault_updates ( faultid, SalesOrderNo, [fault-name], lastedit, lastedittechnician ) Select faultid, SalesOrderNo, [fault-name], lastedit, lastedittechnician From faults END GO
Thanks for looking. Making the follwing changes I get the error: 42000 - [SQL Server]Incorrect syntax near 'AS' CREATE TRIGGER [dbo].[NewTrigger] ON [dbo].[faults] AFTER AS BEGIN IF Exists(Select * From faults) Insert Into fault_updates ( faultid, SalesOrderNo, [fault-name], lastedit, lastedittechnician, Action ) Select faultid, SalesOrderNo, [fault-name], lastedit, lastedittechnician, 'Update' From faults END GO
You appear to have lost the word UPDATE from your original sample - the code should start: CREATE TRIGGER [NewTrigger] ON [dbo].[faults] AFTER UPDATE AS
Thanks for your help on this. Once I got my head round it, it all fits into place.
I'm glad you got it sorted. To help others in the future, please can you accept the answer which solved your problem (tick mark next to the answer), and up-vote (thumbs up) any that helped? Thanks
16 People are following this question.
