November 27, 2017 at 4:37 pm
Hello comunity
I have build this Delete trigger but when i delete a record on my table FT, this trigger dont delete records on the ohter related tables.
This is my trigger code:
create trigger dbo.trg_DeleteRecordRelatedtable
on dbo.ft
for DELETE
as
Set nocount on;
Declare @ftstamp varchar(25)
Declare @myID INT
Select @ftstamp = d.ftstamp, @myID = blDocsCab.ID
From DELETED as d INNER JOIN ft ON d.ftstamp = @ftstamp
INNER JOIN u_blDocsCab as blDocsCab ON blDocsCab.ftstamp = @ftstamp
Where d.ftstamp = @ftstamp
IF TRIGGER_NESTLEVEL() > 1
RETURN;
Delete from u_blDocsLinResume WHERE u_blDocsLinResume.ftstamp = @ftstamp
Delete from u_blDocsCab Where u_bl_DocsCab.ftstamp = @ftstamp
Delete from u_blDocsBO Where u_blDocsBO.id = @myID
Delete from u_blDocsLinDetails Where u_blDocsLinDetails.id = @id
Someone could give me some help.
Many Thanks
Luis
November 27, 2017 at 4:42 pm
The simple answer, your trigger will not work as designed when more than one row of data is deleted from the table ft.
In addition, the variable @ftstamp will be null when the select runs.
November 27, 2017 at 4:55 pm
Can't attest to the validity of the following, but give it a try in a test/development environment:
CREATE TRIGGER [dbo].[trg_DeleteRecordRelatedtable]
ON [dbo].[ft]
FOR DELETE
AS
SET NOCOUNT ON;
DECLARE @ftstamp VARCHAR(25);
DECLARE @myID INT;
DECLARE @DelRecs TABLE (
[ftstamp] VARCHAR(25)
, [myID] INT
);
INSERT INTO @DelRecs SELECT [d].[ftstamp], [blDocsCab].[ID] FROM [DELETED] [d] INNER JOIN [u_blDocsCab] AS [blDocsCab] ON [blDocsCab].[ftstamp] = [d].[ftstamp];
IF TRIGGER_NESTLEVEL() > 1
RETURN;
DELETE FROM [u_blDocsLinResume] WHERE [u_blDocsLinResume].[ftstamp] IN ( SELECT [ftstamp] FROM @DelRecs );
DELETE FROM [u_blDocsCab] WHERE [u_bl_DocsCab].[ftstamp] IN ( SELECT [ftstamp] FROM @DelRecs );
DELETE FROM [u_blDocsBO] WHERE [u_blDocsBO].[id] IN ( SELECT [myID] FROM @DelRecs );
DELETE FROM [u_blDocsLinDetails] WHERE [u_blDocsLinDetails].[id] IN ( SELECT [myID] FROM @DelRecs );
GO -- end batch creating the trigger
November 27, 2017 at 5:05 pm
Hello Lynn
Many thanks for your Reply and explanation.
Best regards
Luis
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply