How to delete child record ! - 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

SQL Server Development (2000)

How to delete child record !

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-26 : 09:35:11

Jafedo writes "I have parent table a follow :

Code1
-----
00001
00002
(Code1 is primary key)

and child table as follow :

Code1 Code2
----- -
00001 A
00001 B
00002 A
00002 B
00002 C

(Code1 is foreign key into parent table)

Constraint on two table before is available
How does I write trigger to delete rows in child table that have foreign key on parent table otomatically when I delete rows in parent table?
My problem is when I delete parent table, foreign key of child table cause error."

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2001-11-26 : 09:50:48


CREATE TRIGGER "Parent_DTrig" ON PARENT_TABLE FOR DELETE AS
SET NOCOUNT ON
/* * CASCADE DELETES TO 'CHILD_TABLE' */
DELETE CHILD_TABLE FROM deleted, CHILD_TABLE WHERE deleted.Code1 = CHILD_TABLE.Code1

jfdn73
Starting Member

2 Posts

Posted - 2001-11-26 : 20:31:09

I have tried that command but it always result following error,

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Child_Parent'. The conflict occurred in database 'sdm', table 'Child', column 'Code1'.
The statement has been terminated.

dshelton
Yak Posting Veteran

73 Posts

Posted - 2001-11-26 : 20:50:33

It fails because the constraint is enforced before
the trigger fires. Are you using 2000? This
has cascade delete option. If not you can use
a trigger to enforce the relationship.

jfdn73
Starting Member

2 Posts

Posted - 2001-11-27 : 05:15:14

I'm SQL Server 7.0 user

I have used following trigger,

CREATE TRIGGER "Parent_DTrig" ON PARENT_TABLE
FOR DELETE
AS
SET NOCOUNT ON /* * CASCADE DELETES TO 'CHILD_TABLE' */
DELETE CHILD_TABLE FROM deleted, CHILD_TABLE
WHERE deleted.Code1 = CHILD_TABLE.Code1

but still result constraint error.

How does I use a trigger to enforce relationship?

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2001-11-27 : 05:43:14

Try using a stored procedure (if you are using ADO) for deleting rows in the child table with the appropriate "code1". You can then use a trigger on the child table to delete the appropriate rows in the parent table. As "dshelton" wrote, you can't use a delete trigger on the parent table because the constraint is enforced before the trigger can fire.

dshelton
Yak Posting Veteran

73 Posts

Posted - 2001-11-27 : 19:59:23

To enforce the relationship with a trigger you need to create
an UPDATE/INSERT trigger on the child tables that references the
parent table when INSERTING or UPDATING the record. The trigger
can rollback the transaction if the parent doesn't exist.

Oh yeah, you will also need to drop the constraint.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources