SQL Server Programming - trigger

Author

trigger - new.value

Gabor Faludi

2006-01-30, 7:06 pm

Hi !

i am a begineer in sql server as far as programming. I would like to have a
trigger that runs on insert or modification and update the "last updated on"
column.

i was assuming i can do sth like
create trigger tgname on table for insert , update as
begin
new.last_updated_on = now();
end

Just conceptually what would be an equvivalent approach for mssql 2k ?

thanks in advance,
gabor

Aaron Bertrand [SQL Server MVP]

2006-01-30, 7:06 pm

You didn't post enough information to provide actual code, but here is a
basic model:

UPDATE
table
SET
last_updated_on = GETDATE()
WHERE
pk_column IN
(
SELECT pk_column FROM inserted
);

You could also use UPDATE...FROM or EXISTS.

"Gabor Faludi" <falu@nospambke.hu> wrote in message
news:drld98$asf$1@namru.matavnet.hu...
> Hi !
>
> i am a begineer in sql server as far as programming. I would like to have
> a trigger that runs on insert or modification and update the "last updated
> on" column.
>
> i was assuming i can do sth like
> create trigger tgname on table for insert , update as
> begin
> new.last_updated_on = now();
> end
>
> Just conceptually what would be an equvivalent approach for mssql 2k ?
>
> thanks in advance,
> gabor
>

Gabor Faludi

2006-01-30, 7:06 pm

hi Aaron ,

thanks for your answer. I am sorry if the information is not sufficient .

i need to buikld a trigger that is fired at each row is inserted or updated.
See what was the original row's value in case of update. and for a specific
column even is in the SQL there is no data i need to put the GETDATE() value
..

so from here on , how do i define such a trigger and how to refer to old
row's values ?

thanks,
gabor
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:uREmcfbJGHA.648@TK2MSFTNGP14.phx.gbl...
> You didn't post enough information to provide actual code, but here is a
> basic model:
>
> UPDATE
> table
> SET
> last_updated_on = GETDATE()
> WHERE
> pk_column IN
> (
> SELECT pk_column FROM inserted
> );
>
> You could also use UPDATE...FROM or EXISTS.
>
>
>
> "Gabor Faludi" <falu@nospambke.hu> wrote in message
> news:drld98$asf$1@namru.matavnet.hu...
>
>

Mark Williams

2006-01-30, 7:06 pm

You can get the "old values" for a row by looking at the deleted virtual
table within the trigger. It sounds like you are writing a trigger for
auditing purposes. Search through this newsgroup and google for "audit
trigger", and you can find examples.

--

"Gabor Faludi" wrote:

> hi Aaron ,
>
> thanks for your answer. I am sorry if the information is not sufficient .
>
> i need to buikld a trigger that is fired at each row is inserted or updated.
> See what was the original row's value in case of update. and for a specific
> column even is in the SQL there is no data i need to put the GETDATE() value
> ..
>
> so from here on , how do i define such a trigger and how to refer to old
> row's values ?
>
> thanks,
> gabor
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> news:uREmcfbJGHA.648@TK2MSFTNGP14.phx.gbl...
>
>
>

Aaron Bertrand [SQL Server MVP]

2006-01-30, 7:06 pm

Okay, looks like I need to create a more complete example to demonstrate
that I have already provided what you are asking for. Note that a trigger
does not fire "at each row", it fires once per statement. So, if you have
an UPDATE without a where clause, the trigger will potentially affect every
row in the table (depending on the code in the trigger, of course).

There are two examples here. The first updates the timestamp on all rows
that were touched by the update. The second example only updates the
touched rows where the value actually changed.

For more information on triggers, please consult Books Online.

CREATE TABLE dbo.foo
(
pk INT PRIMARY KEY,
irrelevant BIT,
last_updated_on DATETIME NOT NULL
DEFAULT CURRENT_TIMESTAMP
);
GO

CREATE TRIGGER dbo.foo_update
ON dbo.foo
FOR UPDATE
AS
BEGIN
IF @@ROWCOUNT > 0
BEGIN
UPDATE dbo.foo
SET last_updated_on = CURRENT_TIMESTAMP
FROM dbo.foo f
INNER JOIN inserted i
ON f.pk = i.pk;
END
END
GO

SET NOCOUNT ON;
INSERT dbo.foo(pk, irrelevant) SELECT 1, 0;
INSERT dbo.foo(pk, irrelevant) SELECT 2, 0;
INSERT dbo.foo(pk, irrelevant) SELECT 3, 1;
GO

SELECT * FROM dbo.foo;
GO

WAITFOR DELAY '00:00:02';
GO

UPDATE dbo.foo SET irrelevant = 1 WHERE pk != 2;
GO

SELECT * FROM dbo.foo;
GO

TRUNCATE TABLE dbo.foo;
GO

DROP TRIGGER dbo.foo_update;
GO

INSERT dbo.foo(pk, irrelevant) SELECT 1, 0;
INSERT dbo.foo(pk, irrelevant) SELECT 2, 0;
INSERT dbo.foo(pk, irrelevant) SELECT 3, 1;
GO

SELECT * FROM dbo.foo;
GO

CREATE TRIGGER dbo.foo_update
ON dbo.foo
FOR UPDATE
AS
BEGIN
IF @@ROWCOUNT > 0
BEGIN
UPDATE f
SET last_updated_on = CURRENT_TIMESTAMP
FROM dbo.foo f
INNER JOIN inserted i
ON f.pk = i.pk
INNER JOIN deleted d
ON f.pk = d.pk
AND i.pk = d.pk
AND i.irrelevant != d.irrelevant;
END
END
GO

WAITFOR DELAY '00:00:02';
GO

UPDATE dbo.foo SET irrelevant = 1 WHERE pk != 2;
GO

SELECT * FROM dbo.foo;
GO

DROP TABLE dbo.foo;
GO

"Gabor Faludi" <falu@nospambke.hu> wrote in message
news:drlnat$fdp$1@namru.matavnet.hu...
> hi Aaron ,
>
> thanks for your answer. I am sorry if the information is not sufficient .
>
> i need to buikld a trigger that is fired at each row is inserted or
> updated. See what was the original row's value in case of update. and for
> a specific column even is in the SQL there is no data i need to put the
> GETDATE() value .
>
> so from here on , how do i define such a trigger and how to refer to old
> row's values ?
>
> thanks,
> gabor
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in
> message news:uREmcfbJGHA.648@TK2MSFTNGP14.phx.gbl...
>
>

Gabor Faludi

2006-01-31, 8:00 am

hi Mark , Aaron ,

thanks for both points . i have managed to build what i wanted and i am posting this for others :

CREATE TRIGGER trg_audit ON [dbo].[Customers]
FOR UPDATE
AS
DECLARE @upd_cust_id varchar(40)

DECLARE updated_rows CURSOR FOR
SELECT customerid from inserted

OPEN updated_rows

FETCH NEXT FROM updated_rows
INTO @upd_cust_id

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

update customers
set modified_on=getdate() , modified_by=user
where customerid=@upd_cust_id

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM updated_rows
INTO @upd_cust_id
END

CLOSE updated_rows
DEALLOCATE updated_rows

regards,
gabor

"Mark Williams" <MarkWilliams@discussions.microsoft.com> wrote in message news:849AA4E1-BE0C-43D8-AB8B-D063DD731DFC@microsoft.com...[color=darkred]
> You can get the "old values" for a row by looking at the deleted virtual
> table within the trigger. It sounds like you are writing a trigger for
> auditing purposes. Search through this newsgroup and google for "audit
> trigger", and you can find examples.
>
> --
>
> "Gabor Faludi" wrote:
>

David Portas

2006-01-31, 8:00 am

Gabor Faludi wrote:
> hi Mark , Aaron ,
>
> thanks for both points . i have managed to build what i wanted and i am posting this for others :
>
> CREATE TRIGGER trg_audit ON [dbo].[Customers]
> FOR UPDATE
> AS
> DECLARE @upd_cust_id varchar(40)
>
>
> DECLARE updated_rows CURSOR FOR
> SELECT customerid from inserted
>
> OPEN updated_rows
>
> FETCH NEXT FROM updated_rows
> INTO @upd_cust_id
>
> -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
> WHILE @@FETCH_STATUS = 0
> BEGIN
>
> update customers
> set modified_on=getdate() , modified_by=user
> where customerid=@upd_cust_id
>
> -- This is executed as long as the previous fetch succeeds.
> FETCH NEXT FROM updated_rows
> INTO @upd_cust_id
> END
>
> CLOSE updated_rows
> DEALLOCATE updated_rows
>
> regards,
> gabor
>

Why would you use a trigger to do this? Look again at Aaron's examples.
Do not use cursors in triggers. Don't use cursors AT ALL if you are a
beginner. Learn to write "proper" SQL first and you'll get on much
better.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Brian Selzer

2006-01-31, 8:00 am

If at least one candidate key doesn't change during the update, then you can
join inserted to deleted on that candidate key to determine what changed on
each row. If all candidate keys can also be changed, then you must reject
updates that affect more than one row, otherwise there's no way to be
certain that a row in the deleted pseudotable matches a row in the inserted
pseudotable.

Here's how to reject updates:

CREATE TRIGGER triggerName ON tableName FOR UPDATE AS
BEGIN
-- don't add any statements here or this trigger may break
IF @@ROWCOUNT != 1
BEGIN
IF UPDATE(key_column_1) OR UPDATE(key_column_2)
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('CAN''T UPDATE A KEY COLUMN AND MORE THAN 1 ROW', 16,
1)
RETURN
END
-- join the deleted and inserted tables here to determine what
happened on each row
END
ELSE
BEGIN
-- cross join the deleted and inserted tables here to determine what
happened on this row
END
RETURN
END

"Gabor Faludi" <falu@nospambke.hu> wrote in message
news:drlnat$fdp$1@namru.matavnet.hu...
> hi Aaron ,
>
> thanks for your answer. I am sorry if the information is not sufficient .
>
> i need to buikld a trigger that is fired at each row is inserted or
> updated. See what was the original row's value in case of update. and for
> a specific column even is in the SQL there is no data i need to put the
> GETDATE() value .
>
> so from here on , how do i define such a trigger and how to refer to old
> row's values ?
>
> thanks,
> gabor
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in
> message news:uREmcfbJGHA.648@TK2MSFTNGP14.phx.gbl...
>
>

Aaron Bertrand [SQL Server MVP]

2006-01-31, 8:00 am

Why are you using a cursor? Do you know what a set is? Did you notice that
the example I posted updates *all* relevant rows in one statement, instead
of tying up database resources doing some needless loop?

"Gabor Faludi" <falu@nospambke.hu> wrote in message
news:drnlcj$n35$1@namru.matavnet.hu...
hi Mark , Aaron ,

thanks for both points . i have managed to build what i wanted and i am
posting this for others :

CREATE TRIGGER trg_audit ON [dbo].[Customers]
FOR UPDATE
AS
DECLARE @upd_cust_id varchar(40)

DECLARE updated_rows CURSOR FOR
SELECT customerid from inserted

OPEN updated_rows

FETCH NEXT FROM updated_rows
INTO @upd_cust_id

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

update customers
set modified_on=getdate() , modified_by=user
where customerid=@upd_cust_id

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM updated_rows
INTO @upd_cust_id
END

CLOSE updated_rows
DEALLOCATE updated_rows

regards,
gabor

"Mark Williams" <MarkWilliams@discussions.microsoft.com> wrote in message
news:849AA4E1-BE0C-43D8-AB8B-D063DD731DFC@microsoft.com...[color=darkred]
> You can get the "old values" for a row by looking at the deleted virtual
> table within the trigger. It sounds like you are writing a trigger for
> auditing purposes. Search through this newsgroup and google for "audit
> trigger", and you can find examples.
>
> --
>
> "Gabor Faludi" wrote:
>

Sponsored Links