October 19, 2004 at 1:19 am
I have an action that performs an insert or an update on a table. One of the columns holds the LastUpdateDate.
I would like to replace the value in this column by the actual getdate() value by means of an after insert,update trigger.
In Oracle, this is simple. You just say :NEW.LastUpdateDate := Sysdate; and that's all.
Is there an equaly simple way of doing this for SQLserver with a T-sql statement ?
Thanks for helping out a beginning T-SQL programmer
October 19, 2004 at 2:26 am
If you can modify your action, you don't need a trigger for this.
For insert, just insert getdate() into the column, for an update, set LastUpdateDate = getdate()
It is possible to use a trigger, though I see this as a last-resort for this 'trivial' problem. Triggers are nice and all, but on the other hand you shouldn't use them if not really needed.
/Kenneth
October 19, 2004 at 3:16 am
It really has to be a trigger.
The scenario is a link between an Oracle DB and SQLserver. The records are inserted/updated on Oracle and receive a last update date. The primary keys of these inserts/changes are logged in mutation tables that are read by SQLserver via a linked Server. The records are then read from Oracle and inserted/updated in SQLServer. I have to change the lastupdatetime to reflect the time that the record arrives on SQLServer. Because we are talking about 50 tables and the name of that lastupdatetime column differs, I can not hardcode them.
A trigger would be the simplest way but how can I do it ?
An example would be great !
October 19, 2004 at 7:47 am
Well, in such a case 'A' trigger won't cut it - you'd need 50 triggers - one for each table.
If I understand it correctly, the insert/update code is on SQL Server..? Can't you just adapt that code to implement getdate() value for it's respective table? (you still need to 'hardcode' 50 triggers otherwise...)
/Kenneth
October 19, 2004 at 10:51 pm
I usually code this kind of trigger like this :
create trigger trigger_name
on table_name
for update
as
update table_name set LastUpdateDate = getdate()
where exists (select * from inserted
where table_name.key_field1 = inserted.key_field1
and table_name.key_field2 = inserted.key_field2)
regards
October 20, 2004 at 12:55 am
I would create a stored procedure that generates the triggers for me. ( as you have a lot of tables ) I do not know the kind of data that is stored, and how are your null settings ( ansi ? ), but here is a working example ( not all types of columns tested/verified ) of a stored procedure that creates a trigger on a given table @tbl. The trigger will set the column @lastupdatetime to getdate for all updates/inserted rows for the table @table. Triggers are names Setlastupdatetime_<@tbl>. You can use this example to generate the text ( use print @sql and not exec (@sql ), or to make a more complex procedure to generate the triggers ...
create proc usp_lastupdatetime
(
@tbl sysname,
@lastupdatetime sysname = 'lastupdatetime'
) as
begin
declare @objid int,@sql varchar(8000)
select @objid = id from sysobjects where name = @tbl and type = 'U'
if @@rowcount != 1
begin
print 'Object ' + @tbl + ' not found or not a user table.'
return 1
end
set @sql = 'create trigger Setlastupdatetime_' + @tbl + ' on ' + @tbl
+ ' for insert,update as begin update ' + @tbl + ' set ' + @lastupdatetime + ' = getdate() from inserted where'
select @sql = @sql + ' and inserted.' + name + ' = ' + @tbl + '.' + name
from syscolumns
where id = @objid
and iscomputed = 0 and isoutparam=0
order by colid
set @sql = replace(@sql,'where and', 'where') + ' end'
exec ( @sql )
end
October 20, 2004 at 5:05 am
Thanks All,
I did already have my script to create the triggers for me (so I shouldn't have to write them manually).
I'ts just unfortunate that a simple method like the one a Illustrated for Oracle does not exist in MSSQLServer. I suppose it still needs to mature a bit.
Cheers
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy