| Author |
Topic |
|
BaggaDonuts
Yak Posting Veteran
52 Posts |
 Posted - 2006-10-30 : 19:54:51
|
| i have an insert, update trigger configured for Table1. Table1 contains a modified date field. The trigger calls a stored procedure which updates the modified date. However, that causes the trigger to fire again. is there a way to ignore a specific field with SQL Server 2000? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-30 : 22:33:28
|
a update trigger always fire whenever the table is updated. You can't stop that.However, you can use COLUMNS_UPDATED() to check and process accordingly
KH |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-31 : 01:48:29
|
| Why call an SProc to update the Modified date, why not just do it in the Trigger?Kristen |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-10-31 : 02:00:20
|
You should be *really* careful when creating triggers and I would recomend not to use them at all. They fire every single time you do an insert, update (Edit: or delete) regardless of the columns you are working on. So unless you are really careful when designing them they can give you quite substantial overhead. I'd recomend doing what you need to do in the stored procedures that do the data manipulation.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-31 : 02:12:36
|
| "I would recomend not to use them at all"You're kidding, right?If BaggaDonuts wants the Modify Date to faithfully change how can you guarantee that every single insert/update will go through an SProc?High chance of a DBA doing a data-fix sooner or later, AND forgetting to tickle the Modify Date, I would reckon!For example: we pull data from an Oracle accounting database and we absolutely 100% rely on the fact that changed records there get a new Modified Date - it saves us pulling the whole 20GB database every hour to just find the handful of records that have changed!Kristen |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-10-31 : 02:24:03
|
I'm not kidding actually but naturally I see your point...but why doesn't the "pull data from oracle db"-procedure or whatever it is update the ModifiedDate? I would assume that there are quite a few other transactions on the target table as well and the trigger would fire for each of them also. I just feel I have better control of what's happening by not using triggers at all and in my limited professional experience (+/- 4 years) I have never had to rely on them. That might change but I hope not!  --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2006-10-31 : 02:26:32
|
| Ahhhh.... But the modify date thingy can be created by using a default datatype CREATE TYPE [dbo].[EditDate] FROM [smalldatetime] NOT NULLGOEXEC sys.sp_bindefault @defname=N'[dbo].[CurrentDate]', @objname=N'[dbo].[EditDate]' , @futureonly='futureonly'That way you don't need triggers Duane. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-10-31 : 02:33:21
|
| Hmmmmmmmmmmmmmmmmm...as far as I can see from BOL this is a SQL 2005 feature but still...can you elaborate a bit on how it works?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-31 : 04:51:32
|
| "why doesn't the "pull data from oracle db"-procedure or whatever it is update the ModifiedDate?"'Coz I'm only SELECTing from Oracle - any Insert/Update on the Oracle DB tickles the Modified Date, so I get that on my next "pull"An alternative would be a TIMESTAMP column and external processes just check if that had changed - but that is a challenging query (to run remotely) without pulling all the data locallyKristen |
|
|
|
BaggaDonuts
Yak Posting Veteran
52 Posts |
Posted - 2006-10-31 : 12:33:42
|
| yes, it is important that this timestamp field gets updated whenever a change occurs. sometimes we script data that modifies the table, so it isn't always a sproc that will perform the update. i had investigated the columns_updated() function, and it appears that it doesn't reference the by name but rather by an algorithm on the column index. i didn't want to do this, because the table schema could possibly change in a future build, and then i'd have to change the trigger. it is a viable option, and i may investigate this further. for now, my solution is to just update the modified date in the sprocs that modify this table, and leave the triggers on other relevant tables. regarding the overhead, this isn't a production database, but rather an internal db. so i'm not TOO concerned about the overhead. thanks for the feedback, it was helpful |
|
|
|
jtrueblood
Starting Member
1 Post |
Posted - 2006-11-01 : 10:09:03
|
| Ditch,I saw your user defined datatype - very, very intersting.CREATE TYPE [dbo].[EditDate] FROM [smalldatetime] NOT NULLGOEXEC sys.sp_bindefault @defname=N'[dbo].[CurrentDate]', @objname=N'[dbo].[EditDate]' , @futureonly='futureonly'However, where does [dbo].[CurrentDate] come from?Thanks,Jeff |
|
|
|
|
Trigger Help