About trigger

kishor choure

14y6.5k

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.

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

kishor choure14y ago
My problem not resolved.please suggest some other solutions.
Nattudurai Eswaramurthy14y ago
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 choure14y ago
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 Marla14y ago
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 choure14y ago
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 jh14y ago
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 choure14y ago
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 jh14y ago
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 choure14y ago
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 jh14y ago
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".

This 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