Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

trigger example modified_date 1

Status
Not open for further replies.

DOGWATCH

Programmer
Joined
Mar 11, 2004
Messages
120
Location
US
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;


 
You want something like this then.
Code:
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)
[noevil]
 
brilliant. this should do the trick.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top