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