Trigger-Foreign Key - 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-Foreign Key

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-01-14 : 12:18:34

Hi have a table Sec_user,Sec_role and Sec_user_role

I have created a foreign key for Sec_user_role table. Now i want to check the records while deleting Sec_user and Sec_Role tables. How can i do thru delete trigger.

Can any one help me please.
Thanks and regards
krishnakumar.C

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-01-14 : 12:19:52

foreign key is fk_secuser_user_id and fk_secrole_role_id

thanks krishnakumar

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2007-01-14 : 23:37:18

Create a trigger which check row existance in Deleted Table. If exist rollback and raiseerror.

Eg.
Create trigger TrgUserDelete on Sec_user
After delete
as
if exists (select 1 from deleted)
begin
RAISERROR ('Deletion from this table is not allowed. Pse contact administrator.',16, 1)
ROLLBACK TRANSACTION
end

Madhu

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-01-15 : 10:40:09

Thanks Madhu

yuva
Starting Member

1 Post

Posted - 2007-01-17 : 02:39:19

Hi... You can even handle this situation while Creating the table itself you can restrict the deletion of a field by using the ON DELETE RESTRICT keyword.

Regards,
Yuvz...

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-01-18 : 05:03:34

hi
for this no need of going for trigger
can do this while creating foreign key table need to specify ON DELETE CASCADE
IF U WANT TO DELETE RECORD

Malathi Rao

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources