trigger not working - 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 2000 Forums

Transact-SQL (2000)

trigger not working

Author

Topic

joemama
Posting Yak Master

113 Posts

Posted - 2005-04-03 : 20:02:32

the below trigger works when a user is inserted or updated it creates a record in the table userProfilelog

but when a user is deleted it does not

can you tell me why?

CREATE trigger trg_UserProfiles
on dbo.Users
for update, insert, delete
as
insert into UserProfileLog(UserID, UserAccess, UserFirstName, UserLastName, UserName, UserEmail, UserStreet, UserCity, UserState, UserZip, UserHomePhone, UserWorkPhone, UserCellPhone, UserPager, EditedBy)
select UserID, UserAccess, UserFirstName, UserLastName, UserName, UserEmail, UserStreet, UserCity, UserState, UserZip, UserHomePhone, UserWorkPhone, UserCellPhone, UserPager, EditedBy
from inserted

nr
SQLTeam MVY

12543 Posts

Posted - 2005-04-03 : 20:12:43

Because for a delete there is no entry in the inserted table.
see
http://www.mindsdoor.net/SQLTriggers/Triggers_1_A_beginners_guide.html

CREATE trigger trg_UserProfiles
on dbo.Users
for update, insert, delete
as
if exists (select * from inserted)
insert into UserProfileLog(UserID, UserAccess, UserFirstName, UserLastName, UserName, UserEmail, UserStreet, UserCity, UserState, UserZip, UserHomePhone, UserWorkPhone, UserCellPhone, UserPager, EditedBy)
select UserID, UserAccess, UserFirstName, UserLastName, UserName, UserEmail, UserStreet, UserCity, UserState, UserZip, UserHomePhone, UserWorkPhone, UserCellPhone, UserPager, EditedBy
from inserted
else
insert into UserProfileLog(UserID, UserAccess, UserFirstName, UserLastName, UserName, UserEmail, UserStreet, UserCity, UserState, UserZip, UserHomePhone, UserWorkPhone, UserCellPhone, UserPager, EditedBy)
select UserID, UserAccess, UserFirstName, UserLastName, UserName, UserEmail, UserStreet, UserCity, UserState, UserZip, UserHomePhone, UserWorkPhone, UserCellPhone, UserPager, EditedBy
from deleted

==========================================
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.

joemama
Posting Yak Master

113 Posts

Posted - 2005-04-03 : 20:27:41

your a god...thanks

can you also steer me towards how to make a foreign key
so i can cascade update and delete?

quote:
Originally posted by nr

Because for a delete there is no entry in the inserted table.
see
http://www.mindsdoor.net/SQLTriggers/Triggers_1_A_beginners_guide.html

CREATE trigger trg_UserProfiles
on dbo.Users
for update, insert, delete
as
if exists (select * from inserted)
insert into UserProfileLog(UserID, UserAccess, UserFirstName, UserLastName, UserName, UserEmail, UserStreet, UserCity, UserState, UserZip, UserHomePhone, UserWorkPhone, UserCellPhone, UserPager, EditedBy)
select UserID, UserAccess, UserFirstName, UserLastName, UserName, UserEmail, UserStreet, UserCity, UserState, UserZip, UserHomePhone, UserWorkPhone, UserCellPhone, UserPager, EditedBy
from inserted
else
insert into UserProfileLog(UserID, UserAccess, UserFirstName, UserLastName, UserName, UserEmail, UserStreet, UserCity, UserState, UserZip, UserHomePhone, UserWorkPhone, UserCellPhone, UserPager, EditedBy)
select UserID, UserAccess, UserFirstName, UserLastName, UserName, UserEmail, UserStreet, UserCity, UserState, UserZip, UserHomePhone, UserWorkPhone, UserCellPhone, UserPager, EditedBy
from deleted

==========================================
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.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-04-03 : 20:43:30

alter table mytbl add constraint xxxxx foreign key (fld1, fld2) references othertable (fld1, fld2)

Have a look in bol.

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources