Is there a Before Update Trigger with SQL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff - 03 May 2005 16:33 GMT

I would like to set the User_Name() and GetDate() in a table before my
insert and update triggers start. Is there a way of doing this before the
trigger? Right now I update these fields after the trigger but it ends up
causing an unnecessary re-trigger to update the fields and I don't want to
set the username and date on the front-end.

Reply to this Message

Graham R Seach - 04 May 2005 13:14 GMT

Jeff,

As with Jet, you can define a DefaultValue in SQL Server, using GetDate()
and SUSER_SNAME(). But SQL Server also allows you to specify INSTEAD OF
triggers, like so:

CREATE TRIGGER procMyInsteadProc ON dbo.Table1
INSTEAD OF INSERT
AS
INSERT INTO Table1 (MyDate, MyUser)
VALUES (DateAdd(month, 1, GetDate()),
SUSER_SNAME() + ' eats worms!')

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

>I would like to set the User_Name() and GetDate() in a table before my
> insert and update triggers start. Is there a way of doing this before the
> trigger? Right now I update these fields after the trigger but it ends up
> causing an unnecessary re-trigger to update the fields and I don't want to
> set the username and date on the front-end.

Reply to this Message

Jeff - 04 May 2005 14:52 GMT

Thanks, works like a charm

Reply to this Message