trigger example modified_date 1

[DOGWATCH]

need the code to do this on sql2000 should be simple.

here is the table:

comment_id integer primary key,
user_id not null references users,
ip_address varchar(50) not null,
modified_date date not null,
content varchar (255)

I need a really sumer basic trigger where if any of these
filed are inserted or updated it should update the modified_date filed to current timestamp now();

found a good example how to do this but it was written in
PL/SQL. I need something that would work on updates or inserts of all fields written in T-SQL for SQL 2000 db.
any ideas?

create trigger general_comments_modified
before insert or update on content
for each row
begin
:new.modified_date := sysdate;
end;

 

[mrdenny]

You want something like this then.

CREATE TRIGGER trg_TableName ON TableName
FOR INSERT, UPDATE
AS
BEGIN
    update TableName
    set TableName.modified_date = getdate()
    where comment_id in (select comment_id from inserted)
END

For this to work as it you will need to disable recursive triggers on your SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[DOGWATCH]

brilliant. this should do the trick.