Trigger question
Eric G. Harrison
I have a table that contains LAST_UPDATED_DATE and LAST_UPDATED_TIME
fields. I would like to be able to, on an INSERT or UPDATE, use a
trigger to fill these fields in. Now, I have tried the few things
that I could think of, to no avail. Would someone please post an
example that I can work with? FYI : This is on MS Sql Server for NT,
v4.21. Thanks! - Eric.
Joel Friedman
->Hi All! I'm new to triggers, so if this is stupid, please be gentle!
->I have a table that contains LAST_UPDATED_DATE and LAST_UPDATED_TIME
->fields. I would like to be able to, on an INSERT or UPDATE, use a
->trigger to fill these fields in. Now, I have tried the few things
->that I could think of, to no avail. Would someone please post an
->example that I can work with? FYI : This is on MS Sql Server for NT,
->v4.21. Thanks! - Eric.
create trigger
on table
for insert, update
as
declare @curDate char(10)
declare @curTime char(8)
if (select count(*) from inserted) = 0
begin
return
end
select @curDate = convert(char(10), getdate(), 101),
@curTime = convert(char(8), getdate(), 8)
update table
set T.LAST_UPDATED_DATE = @curDate,
T.LAST_UPDATED_TIME = @curTime
from table T,
inserted I
where T.KEY_VALUE = I.KEY_VALUE
if @@error != 0
begin
raiserror 20000 "Unable to update table"
end
---
====================================================================
|
_/ _/_/_/ _/_/_/ _/ | Joel Friedman (consultant)
_/ _/ _/ _/ _/ |
_/ _/ _/ _/_/ _/ | - "If you've got a solution, let me
_/ _/ _/ _/ _/ _/ | find the problem."
_/_/ _/_/_/ _/_/_/ _/_/_/ |
| g9u...@fnma.com
====================================================================
Cliff High
>From: e...@mcs.com (Eric G. Harrison)
>Subject: Trigger question
>Date: 3 Oct 1994 03:07:36 GMT
One approach to save a little overhead (static and dynamic)
would be to create a column as datetime and set a
default as follows:
da ta da da NULL,
MyDate datetime)
create default DOE as getdate()
/* DOE = Date of edit*/
sp_bindefault DOE, "TableName.MyDate"
Bon chance
Cliff High
Tenax SE
Creators of SP_VC(c) the server based code configuration management tool for
SQL Server.
ch...@halcyon.com
1-800-261-1686
Henrik Hoel
Hi everybody.
In article 3...@News1.mcs.com, e...@mcs.com (Eric G. Harrison) writes:
->Hi All! I'm new to triggers, so if this is stupid, please be gentle!
->I have a table that contains LAST_UPDATED_DATE and LAST_UPDATED_TIME
->fields. I would like to be able to, on an INSERT or UPDATE, use a
->trigger to fill these fields in. Now, I have tried the few things
->that I could think of, to no avail. Would someone please post an
->example that I can work with? FYI : This is on MS Sql Server for NT,
->v4.21. Thanks! - Eric.
I have a follow up question to this artical. We have exactly the same scenario
with two fields that must be updated on an INSERT or UPDATE. The question I have,
is a performance one. Which is best (fastest), the trigger solution
or using an special select statement to retrieve the current date and time and
put that information in the INSERT or UPDATE command?
Best regards, Henrik
Q Vincent Yin
>I have a follow up question to this artical. We have exactly the same scenario
>with two fields that must be updated on an INSERT or UPDATE. The question I have,
>is a performance one. Which is best (fastest), the trigger solution
>or using an special select statement to retrieve the current date and time and
>put that information in the INSERT or UPDATE command?
The method I use is to let the client application maintain the last_update
user and timestamp, but the trigger and rules will make sure that those
values are set correctly. As far as I can see, it provides the maximum
efficiency while ensuring the quality of data. In the script below,
the trigger ensures that the client program does not forget to supply
a value for last_update_time, while the rule ensures that the client
program supplies the current time (allows 20 minutes difference between
the timers on different machines.) All those restrictions are bypassed
for dbo (because we sa want to do whatever with the table!)
Any enhancement suggestions will be greatly apprieciated.
=========================================================
sp_addtype timestamp_type, "smalldatetime"
go
create rule R_timestamp as
user_name() = 'dbo' or abs(datediff(minute, @value, getdate())) < 20
go
sp_bindrule R_timestamp, "timestamp_type"
go
create default D_timestamp as getdate()
go
sp_bindefault D_timestamp, "timestamp_type"
go
create table my_table (
...
last_update_time timestamp_type not null,
)
go
create trigger my_trigger for insert, update on my_table as
...
if user_name()!='dbo' and exists (select * from deleted)
begin
if NOT update(last_update_time)
begin
raiserror 99999 "ERR: Must update last_update_time."
rollback tran
return
end
end
go
--
Q Vincent Yin | Repeat
um...@mctrf.mb.ca | delete(next_bug);
| Until 0 = 1;
Kylo Ginsberg
stupid questions ...
In article <389h3s$4...@canopus.cc.umanitoba.ca>
um...@raphael.mctrf.mb.ca (Q Vincent Yin) writes:
>create trigger my_trigger for insert, update on my_table as
> ...
> if user_name()!='dbo' and exists (select * from deleted)
> begin
> if NOT update(last_update_time)
> begin
> raiserror 99999 "ERR: Must update last_update_time."
> rollback tran
> return
> end
> end
>go
I see how this will work on an update, but on an insert won't the "exists
(select * from deleted)" always evaluate false and thus the trigger is
guaranteed inconclusive for inserts?
And won't the default bound to last_update_time mean that update(...) above
will evaluate true for all inserts whether or not the client app actually
specified that column?
Any corrections or clarifications welcome. Thanks in advance!
--Kylo
--Kylo Ginsberg
kylo...@mhs.unc.edu
(919) 962-9074
David Ferrington
to make it cleaner, bind the default to a user defined datatype and user that
as the column type whenever you need this type of column.
just insert/update a row without including this column to have it pick up the
default datetime.
even better, insert/update this a value to override the default.
however, if you think someone will use isql etc to insert/update and could
"cheat" by specifying the col then you will have to use a trigger
that's what triggers are for and the're good at it.
Henrik, avoid the special select, unless you have 100's of such inserts, using
the default will be faster - using the trigger will be faster still.
---
---------------------------------------------------------------
| David J Ferrington The art of RDBMS's is a "Syance"! |
| Swiss Bank Corp |
| London Email: fer...@gb.swissbank.com |
|--------------------------------------------------------------
| Normal Disclaimers Apply :-) |
---------------------------------------------------------------
-D
Q Vincent Yin
# In article <389h3s$4...@canopus.cc.umanitoba.ca>
# um...@raphael.mctrf.mb.ca (Q Vincent Yin) writes:
# >create trigger my_trigger for insert, update on my_table as
# > ...
# > if user_name()!='dbo' and exists (select * from deleted)
# > begin
# > if NOT update(last_update_time)
# > begin
# > raiserror 99999 "ERR: Must update last_update_time."
# > rollback tran
# > return
# > end
# > end
# >go
# I see how this will work on an update, but on an insert won't the "exists
# (select * from deleted)" always evaluate false and thus the trigger is
# guaranteed inconclusive for inserts?
# And won't the default bound to last_update_time mean that update(...) above
# will evaluate true for all inserts whether or not the client app actually
# specified that column?
Now it's agreed that the trigger is correct for UPDATE. Let me explain more
about INSERT.
The trick is that the "last_update_time" has a rule bound to it that says
create rule foo as
user_name() = 'dbo' or abs(datediff(minute,@value,getdate())) < 20
"last_update_time" is declared to be "not null". So in an insert operation,
this rule is guaranteed to fire whether the value for "last_update_time" is
supplied explicitly by the front end or implicitly by the default. Since
the rule has taken care of the value, my trigger won't bother checking it
again. That "if exists (select * from deleted)" is exactly to test whether
the current operation is insert or update.
The same trick is applied to update operations. That's why my trigger only
enforces the user to update last_update_time to _some_ value in an update
operation, but doesn't care whether the new value is correct or not.
The default bound to "last_update_time" is just a convenience for front end.
It by no means contribute to the quality of the data.