trigger

[Andrei25]

Hi!
I've made 2 triggers: one for insert and one for update that should put in the every table the user who makes an insert or update a record and also the system date.
but, when I make an insert in Enterprise Manager, everything is ok, its updating just the record that suffer the changes.When I make a select to see all records I see that it updated all records in the table. WHERE IS THE MISTAKE?

CREATE TRIGGER [trg_update] ON [dbo].[country]
FOR update
AS
declare @userName varchar(50)
set @userName= (select user_id from [dbo].[crepart_user]
where username= current_user )

update [dbo].[country]
set dbo.country.update_date= CONVERT( datetime, GETDATE())
update [dbo].[country]
set dbo.country.update_user = @userName

CREATE TRIGGER [trg_insert] ON [dbo].[country]
FOR INSERT
AS
declare @userName varchar(50)
set @userName= (select user_id from [dbo].[crepart_user]
where username= current_user )

update [dbo].[country]
set dbo.country.create_date= CONVERT( datetime, GETDATE())
update [dbo].[country]
set dbo.country.create_user = @userName

 

[MartinCroft]

Hi

your triggers do not specify which rows to be inserted or updated you have a global update which would update all rows with the same timestamp. I created a replica of the problem, buty not knowing the key on the table i created an identity column called rowid, and amended to use 1 update. The trigger uses inserted a 'ghost' table that is created when using trigger see BOL for more details.


ALTER TRIGGER [trg_insert] ON [dbo].[country]
FOR INSERT
AS
declare @userName varchar(50)
set @userName= (select current_user )


update a
set a.create_user = @userName
,a.create_date = CONVERT( datetime, GETDATE())
FROM
[dbo].[country] a
JOIN
inserted b on a.rowid=b.rowid
WHERE
a.rowid =b.rowid


Hope this helps

Mart