About trigger

Question

I create four tables in sql 2005 as

StudentTbl

s_id(PK)	int
s_name	varchar(30)
s_add	varchar(50)

DeptTbl

d_id(PK)	int
d_name	varchar(50)
s_id(FK)	int

STbl_backup

s_id(PK)	int
s_name	varchar(30)
s_add	varchar(50)

DTbl_backup

d_id(PK)	int
d_name	varchar(50)
s_id(FK)	int

and I set StudentTbl cascade with DeptTbl and STbl_backup cascade with DTbl_backup.

I write Trigger for Delete on StudentTbl and DeptTbl like this way:

create trigger Backup_stbl
on StudentTbl
for delete
as
begin
declare @s_id int
declare @s_name varchar(30)
declare @s_add varchar(50)

select @s_id=s_id , @s_name=s_name,@s_add=s_add from deleted
insert into STbl_backup values(@s_id,@s_name,@s_add)
end

create trigger Backup_dtbl
on DeptTbl
for delete
as
begin
declare @d_id int
declare @d_name varchar(30)
declare @s_id int

select @d_id=d_id , @d_name=d_name,@s_id=s_id from deleted
insert into DTbl_backup values(@d_id,@d_name,@s_id)
end

Finally I inserted four records .

Expectation of trigger Backup_stbl is to get deleted records of StudentTbl and insert into the STbl_backup table
same is the case for Backup_dtbl.

but when I write following query:

delete from StudentTbl where s_id=2

then It display me error like :

Msg 547, Level 16, State 0, Procedure Backup_dtbl, Line 15
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_DTbl_STbl". The conflict occurred in database "test", table "dbo.STbl", column 's_Id'.
The statement has been terminated.

I Provide You all details If you understand my problem then please suggest me solution. Thanks in advance.

kishor choure · Answer

My problem not resolved.please suggest some other solutions.

Nattudurai Eswaramurthy · Answer

Hi I found solution to ur problem. plz see the below code; create table Student(s_id int primary key s_name varchar(30) s_add varchar(50)) create table Dept(d_id int primary key d_name varchar(30) s_id int foreign key references Student(s_id) on delete cascade) create table Student_bkp(s_id int primary key s_name varchar(30) s_add varchar(50)) create table Dept_bkp(d_id int primary key d_name varchar(30) s_id int foreign key references Student_bkp(s_id)on delete cascade) alter table Dept_bkp drop constraint FK__Dept_bkp__s_id__22FF2F51 select * from Student select * from Dept select * from Student_bkp select * from Dept_bkp insert into Student select 1'Nattu''DPM' insert into Student select 2'Priya''AKP' insert into Dept select 101'IT'2 create trigger T_Student on Student for delete as begin declare @s_id int declare @s_name varchar(30) declare @s_add varchar(50) select @s_id=s_id  @s_name=s_name@s_add=s_add from deleted insert into Student_bkp values(@s_id@s_name@s_add) end create trigger T_Dept on Dept for delete as begin declare @d_id int declare @d_name varchar(30) declare @s_id int select @d_id=d_id  @d_name=d_name@s_id=s_id from deleted insert into Dept_bkp values(@d_id@d_name@s_id) end delete from Student where s_id=1 delete from Dept where s_id=1

kishor choure · Answer

I got the forign key or reference key problem. As per your solution I don't want to remove cascading relationship of StudentTble and DeptTbl When I remove relationship on STbl_backup and DTbl_backup then Trigger works fine. I Don't want to delete record s from DeptTbl manually. I requiered records get deleted automatically. Finally you said that  keep other trigger same and keep cascade realationshi between STbl_backup and DTbl_backup  but I don't understand.

Sukesh Marla · Answer

Try to understand exatcly What happening here When you fire delete query on StudentTbl 1.it deletes records from DeptTbl Before deleting from DeptTbl(because of cascade) 2.AfterRecords are delete from DeptTbl because of triggers records are tried to insert in DTbl_backup which is referecing from STbl_backup (Remeber Records are not deleted from StudentTbl yet so no records are inseretd into STbl_backup) So you get foreign key error. --------------------------------- One solution you can do is Remove cascading relationship and write entire logic inside Trigger do in this way Create Trigger on StudentTbl and inside it 1.Insert corressponding records inside STbl_backup 2.Delete All Records From DeptTbl manually keep other trigger same and keep cascade realationshi between STbl_backup and DTbl_backup Hope you understod Check this is corerct answer if it helped.

kishor choure · Answer

first mistake i have made is when i writting on the this forum. and not in actual trigger.So outcome is same as error. second I set cascading action on relationship of StudentTbl and DeptTbl as well as STbl and DTbl So When I delete the record from my master Tbl (here StudentTbl) all the records of that particular s_id get deleted from Child Tbl(here DeptTbl) but the run time or logical error is occured inside my Bacup_dtbl trigger.

hj jh · Answer

yes ... so now whats the outcome... try to change the query by writing 2 delete statements: 1 for first deleting from the child table & then 2nd for deleting from the master table See whats the outcome!

kishor choure · Answer

In above by mistake I written inside Backup_dtbl trigger insert query as on Stbl table .but Actually this insert is on Dtbl. I Corrected this now.

hj jh · Answer

There are two types of triggers: FOR triggers i.e. FOR DELETE and INSTEAD OF triggers i.e. INSTEAD OF DELETE Try to first change the query by deleting the record from child table before deleting it from master table.

kishor choure · Answer

In above by mistake I written inside Backup_dtbl trigger insert query as on Stbl table .but Actually this insert is on Dtbl. I Corrected this now. I am not understand what you saying that INSTEAD OF DELETE instead of FOR DELETE.

hj jh · Answer

Hi I think there can be 2 issues in this as per the code that you have posted. 1: Pls check your 2nd trigger Backup_dtbl. In this see the query: select @d_id=d_id  @d_name=d_name@s_id=s_id from deleted insert into STbl_backup values(@d_id@d_name@s_id) Here  I think you are creating trigger on DeptTbl i.e. your dept table but the insert goes in STBLbackup I think that can be 1 problem. Another is if your trigger query is correct then do one thing try to use INSTEAD OF DELETE instead of FOR DELETE. T his is because the dept table's have dependency on Stbl's & so this error has to occur & that is why  either remove the foreign keys or try to use Instead of Delete trigger. ONE MORE THING you have to make sure is whenever you are deleting record from your parent table (in which primary key is defined) then before that  delete the record from your child table(in which foreign key is defined). So pls change the query accordingly. Perhaps it might solve your problem. If not  then let me know further. Please accept as answer if your problem is resolved