begginer Trigger question - 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 2005 Forums

Transact-SQL (2005)

begginer Trigger question

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2013-02-22 : 06:16:00

Hi All,

This first time i use a trigger.
what i want to accomplish is if a customer change there details.
i copy the Original details to another table.
and only the record that was changed.

I had this but that did work

ALTER TRIGGER TR_Customer_original
ON dbo.T_Customer
for UPDATE
AS
BEGIN
INSERT INTO dbo.T_Customer_Changed
SELECT T_Customer.*
FROM T_Customer
SET NOCOUNT ON;

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-22 : 06:48:15

At trigger runtime there are "tables" named deleted and inserted.
In case of Update the new data is in inserted and the old data is in deleted.
So in your example you have to select your data from deleted instead of T_Customer.

btw. SET NOCOUNT ON should be the first and not the last statement...

Too old to Rock'n'Roll too young to die.

mike13
Posting Yak Master

219 Posts

Posted - 2013-02-22 : 06:59:16

hi webfred,

thanks for the info.
But like i said I'm beginner with trigger and know idea how to accomplish what you just explained.

could you give me a syntax example please

thanks a lot,

Mike

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-22 : 07:15:03

[code]-- create the example tables
create table t_customer(id int, custname varchar(255))
create table t_customer_changed(id int, custname varchar(255))

-- create the trigger
create trigger t_customer_change_track
on t_customer
for update
as
begin
set nocount on
insert t_customer_changed
select * from deleted
set nocount off
end

-- create some testdata
insert t_customer(id, custname)
select 1, 'mike' union all
select 2, 'Fred'

-- show testdata
select * from t_customer

select * from t_customer_changed

-- do the update
update t_customer
set custname = 'Mike13' where id=1

-- show testdata
select * from t_customer

select * from t_customer_changed
[/code]

Too old to Rock'n'Roll too young to die.

mike13
Posting Yak Master

219 Posts

Posted - 2013-02-22 : 08:47:31

Hi webfred,

I didnit need everything just the trigger would have been enough ;-)
But thanks a lot !

Danku

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-22 : 08:51:02

One thing inside the trigger should be done a better way:

I have posted:
set nocount on
insert t_customer_changed
select * from deleted
set nocount off

better:
set nocount on
insert t_customer_changed(id, custname)
select id, custname from deleted
set nocount off

It is never a good idea to use * instead of the column names...

Too old to Rock'n'Roll too young to die.

mike13
Posting Yak Master

219 Posts

Posted - 2013-02-22 : 10:56:18

hi webfred,

I understand, it is just a lot of fields ;-)
thats why i used the * in my question

tnx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources