Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
 Posted - 2005-05-24 : 19:30:00
|
A new requirement is that all tables will contain 4 auditing fields to indicate by whom and when a row was created and by whom and when a row was modified. The first is easy enough to setup by using DEFAULT values. The second, it seems would best be served by a trigger. One solution is to have a trigger like:CREATE TRIGGER dbo.myTrigger ON myTable FOR INSERT, UPDATE AS UPDATE myTable SET UpdatedBy = SUSER_SNAME(), UpdatedDate = getdate() FROM myTable JOIN inserted on myTable.PrimaryKey = inserted.PrimaryKey This, of course could lead to recursive firings of the trigger, if that option is enabled. Currently on this database it is disabled.Now, my question: Would our fine guru panel that has assembled here go with this approach, or use INSTEAD OF triggers instead, or use a completely different approach? Your opinions?---------------------------EmeraldCityDomains.com |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-24 : 21:12:56
|
| If all access is via SPs then you could put this in the sp.Otherwise I would go for a trigger - you could also insert into another table linked via the pk.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-05-25 : 11:18:48
|
| Is it better to use an AFTER trigger, like in my example, and have recursive triggers turned off, or use an INSTEAD OF trigger and make my own update with the option to turn recursive triggers on later? In fact, why would you want recursive triggers to be enabled? The only example I can think of might be with an adjacency hierarchy model. Is that true? Other times when people find it useful?---------------------------EmeraldCityDomains.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-05-26 : 01:36:23
|
| An AFTER trigger is going to do the update twice. An SProc (which is how we do it) will only do the one update. I supopse an INSTEAD OF trigger will only do one update, but I'm guessing it would be slower than doing it in the SProc [assuming that that is an option!]We stopped doing it in Triggers because when we pulled some data from elsewhere (INSERT INTO Local SELECT * FROM remote), which already had the Updater stuff in it, the Updater info got overwritten with today's date and the UserName of the DBA doing the import - which wasn't really the latest "editor"Kristen |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-05-26 : 12:36:24
|
| Hmmmm... That's a good point Kristen, but on the other hand, if one of our power users or admins who has the permissions to act directly on the data makes a change, it would be nice to still track that.---------------------------EmeraldCityDomains.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-05-26 : 14:30:21
|
| Sure - Six of one, Half a dozen of the other - Your call!Kristen |
|
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2006-01-30 : 16:50:23
|
| How much of a leap would it be to use audit tables instead, to track your changes? See here:[url]http://www.sqlteam.com/item.asp?ItemID=11123[/url] |
|
|
|
|
|
|
|
|
Recursive Trigger Options