trigger to be fired on update!!!!its urgant!!!

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 2000 Forums

Transact-SQL (2000)

trigger to be fired on update!!!!its urgant!!!

Author

Topic

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2002-09-25 : 07:18:10

hello,
i have master table user_master ,with a field 'email' and there is another table cards, which also has a field 'email' in it ,both do not have any relationships.but if the record for a email exists in user_master and cards, and if the record for email in user_master is updated ,then corresponding record in cards should also be updated ,I am not able to write the trigger for it..
please help ...
its urgent!!!!...
thnxs in advance.
harshal

Edited by - harshal_in on 09/25/2002 07:20:06

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-25 : 07:32:40

No relationship?
You mean you just want any email in cards with that name to be updated?
It will need the primary key from user_master to work

create trigger tr_user_master on user_master for update
as
update cards
set email = i.email
from inserted.i, deleted.d
where i.PK = d.PK
and cards.email = d.email
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2002-09-25 : 07:49:53

i have found the solution :::
alter trigger user_master_update on user_master for update
as

if exists(select * from inserted)
begin

update cards
set email = i.email
from cards c,deleted d,inserted i
where d.email = c.email
print 'executed successfully'
end

thanxs nr ...
harshal

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-25 : 07:55:50

Nope - see my comment above.
You need to join to the inserted table.

No need for the if exists if you put that join in but doesn't hurt.

Yours will work if you only ever update a single row or only updated all occurances of a single email to the same email then it should probably be

update cards
set email = (select distinct i.email from inserted)
from cards c,deleted d
where d.email = c.email

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 09/25/2002 07:59:36

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources