Delete trigger dont fire on related tables

  • 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

  • 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.

  • 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

  • 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