Trigger in SQL 2008 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trigger in SQL 2008

Author

Topic

mfkabir
Starting Member

2 Posts

Posted - 2011-08-16 : 12:01:49

i have created a trigger like this. but when i insert a row it updates all the column of MUTATION_DT.i actually want to update the inserted one.

CREATE TRIGGER PER_PERSONS_NAME
ON [dbo].[PER_PERSONS]
For INSERT, UPDATE
as
begin
Update [dbo].[PER_PERSONS]
set NAME = SURNAME + ' ' + FIRSTNAMES,
MUTATION_DT = getdate () where PERSON_ID = PERSON_ID
end

GO

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-16 : 12:16:09

where PERSON_ID = PERSON_ID will be true for every row. This is what you want I think

Update	p
set	NAME = i.SURNAME + ' ' + i.FIRSTNAMES, 
	MUTATION_DT = getdate ()
FROM	[dbo].[PER_PERSONS] p
JOIN	INSERTED i
On	i.PERSON_ID = p.PERSON_ID

mfkabir
Starting Member

2 Posts

Posted - 2011-08-16 : 13:08:41

it works. thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-17 : 04:56:39

i actually want to update the inserted one.

by this do you mean update date only for inserts and not updates?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-17 : 05:01:23

quote:
Originally posted by visakh16

i actually want to update the inserted one.

by this do you mean update date only for inserts and not updates?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I think this is what OP means. The original trigger updates all rows of the table and not only the newly inserted records

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources