SQL2005 trigger
- Does SQL 2005 has before insert/update trigger?I'm having some trouble creating some audit triggers. If anyone have sample audit trigger, which you can give me that would be great.
All my tables have a "creationDate" and a "modificationDate" column. These columns have a not null constraint. I want to create a "before insert" trigger and a "before update" trigger that will set the sysdate to these columns. It looks like sql server 2005 does not have a "before insert" or a "before update" trigger.
Also one of the tables PRICE_SHEET has a column called p_NUMBER which needs to be set in the format 'mmddyy-#####', where ##### is the PRICE_SHEET_ID which is the primary key of the column. The column has the "not null" constraint. I want to write a "after insert" trigger that sets the DPT_NUMBER in the above format.
I guess I have to use the "after insert" trigger because in Sql server I can get the inserted primary key only after the row gets inserted. But I can't insert the row because of the not null constraint. Any one know how I can do this?
Thanks in advance
-
Just put a default of getDate() on those columns. No need to have an insert trigger then. YOu'll simply need an afeter insert for the midified by and date columns.
Why do you add the date to the id of the row?
-
great suggestion. Thanks
-
I just want to point out that it is not possible to have a before insert trigger that modifies the record that you haven't yet inserted.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Well actually there's a way. In the instead of insert you just modify or swap the value from the inserted table. But in fact you can't change the underlying data because it doesn't exist yet!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply