April 14, 2009 at 1:52 pm
I have on delete trigger. Based on an id I will have to delete all child table records & finally delete the record from the parent table.
I have the below code in the on delete trigger. Whether it is necessary to raiseerror inside the catch block or its dangerous to do it inside a trigger?
based on the below code if any error occurs inside the begin try block it would never appear to the statement that called or the application that issued the delete statement on table ABC.
CREATE TRIGGER [dbo].[ABC_delete]
ON [dbo].[ABC]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON
if (TRIGGER_NESTLEVEL()<2)
BEGIN TRY
BEGIN TRANSACTION
DELETE dbo.B1 FROM dbo.B1
INNER JOIN DELETED D ON dbo.B1.ID = D.ID
DELETE dbo.C1 FROM dbo.C1
INNER JOIN DELETED D ON dbo.C1.ID = D.ID
DELETE dbo.D1 FROM dbo.D1
INNER JOIN DELETED D ON dbo.D1.ID = D.ID
DELETE dbo.ABC FROM dbo.ABC
INNER JOIN DELETED D ON dbo.ABC.ID = D.ID
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
END
GO
April 15, 2009 at 7:36 am
If the column "ID" is a primary key or unique key in the dbo.ABC table, you can create foreign keyes with "ON DELETE CASCADE" that references the ABC.ID column from each of these child tables. When a record is deleted from the dbo.ABC table, it will cascade deletes to all children tables, and you dont have to worry about writing or maintaining a trigger.
However, if this is not an option, I don't think a RAISERROR in your trigger will cause problems. I've added some sample code where I forced an error in the trigger and did not see any issues. I've also included sample code for the cascading delete:
code with trigger forcing error:
CREATE TABLE dbo.x1 (
x1id int identity(1,1) PRIMARY KEY
, val varchar(20))
GO
CREATE TABLE dbo.x2 (
x2id int identity(1,1)
, x1id int )
GO
CREATE TRIGGER dbo.trg_x1_delete ON x1 INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
DELETE dbo.x2
FROM dbo.x2
JOIN DELETED D
ON dbo.x2.x1id = D.x1id
-- this should force an error since val is only a varchar(20)
UPDATE dbo.x1
SET val = 'xxxxxxxxxxxxxxxxxxxxxxxxxx'
DELETE dbo.x1
FROM dbo.x1
JOIN DELETED D
ON dbo.x1.x1id = D.x1id
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
RAISERROR ('Error Message', 16, 1)
END CATCH
END
GO
insert into dbo.x1(val)
select 'a'
union select 'b'
union select 'c'
insert into dbo.x2(x1id)
select x1id
from dbo.x1
select *
from x1
select *
from x2
delete
from x1
where x1id = 1
code with cascading delete:
CREATE TABLE dbo.x1 (
x1id int identity(1,1) PRIMARY KEY
, val varchar(20))
GO
CREATE TABLE dbo.x2 (
x2id int identity(1,1)
, x1id int FOREIGN KEY REFERENCES x1(x1id) ON DELETE CASCADE)
GO
insert into dbo.x1(val)
select 'a'
union select 'b'
union select 'c'
insert into dbo.x2(x1id)
select x1id
from dbo.x1
select *
from dbo.x1
select *
from dbo.x2
delete
from dbo.x1
where x1id = 3
select *
from dbo.x1
select *
from dbo.x2
April 15, 2009 at 8:52 am
beautiful. Thanks, Brad.
I had the same code in my mind but was unclear if I can use them. Cascade delete is not an option for me. As we dont have control over what would be deleted on the tables. Moreover, if we change any table as self-referencing or candidate keys then we could potential have some problems.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply