Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2002
    Posts
    42

    Unanswered: Trigger Question

    I have a table that I would like to create an update trigger on. When a row is updated I want to check a certain column to see if it was updated to a certain value, if it is updated to a certain value I want to delete rows from other tables based on the updated value.

    I have tried to get this working, but have been unsuccessful. Can this be done? If so, how do I proceed?

    Thanks...

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by errodr
    Can this be done?
    Of course - or at least there is not enough info to say "no".

    Quote Originally Posted by errodr
    I have tried to get this working, but have been unsuccessful.
    What have you tried? Fancy sharing some code? Easier to adjust than to start green field. You know about the inserted and deleted virtual tables and how you can use them I presume?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Posts
    42
    Here is what I have tried:

    Code:
    CREATE TRIGGER [dbo].[trg_Clean_Up] ON dbo.CS_INDEX 
    AFTER UPDATE
    AS
    
    DECLARE @INDEX AS INT,@note as varchar(255)
    SELECT @INDEX =  INDEX_ID FROM INSERTED
    SELECT @NOTE =  substring(CMP_PROCESS_NOTE,1,5) FROM INSERTED
    
    
    IF @NOTE = "error"
    BEGIN
    		DELETE  FROM CS_T1 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T2 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T3 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T4 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T5 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T6 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T7 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T8 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T9 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T10 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T11 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T12 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T13 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T14 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T15 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T16 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T17 WHERE INDEX_ID = @INDEX
    		DELETE  FROM CS_T18 WHERE INDEX_ID = @INDEX
    END
    I know I am not actually inserting a record, but I thought I could use the virtual INSERTED table.

    Would be glad for any help.

    Thanks...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by errodr
    I know I am not actually inserting a record, but I thought I could use the virtual INSERTED table.
    When you use an update trigger, the SQL Server engine "deletes" the rows to be updated and then "inserts" the updated rows. It doesn;t actually do this - this is a logical process however it means you get to use the virtual deleted table (affected rows before the insert) and inserted table (affected rows after the insert). So yes - in short you are correct.

    The main problem with your code is that it is not set based - it assumes only a single row is updated. There could be any number. "But my procs only update one row at a time" is not sufficient - there will be adhoc\ admin\ dataload etc changes that will affect more than one row - trust me.

    So - you need need to link the inserted table to those 20 or so tables in queries and use the logic you have to produce variables to filter the requisit rows with a where clause.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2005
    Posts
    122
    You are doing a classic mistake, and that is to assume that you never update more than one row at a time.

    The inserted/deleted tables contain the same number of records that you query updated.

    So assigning INDEX_ID to a variable is wrong.

    Instead you must execute a delete fromthe tables where INDEX_ID is IN the INDEX_ID of the rowset of the inserted table. Like this:
    Code:
    DELETE  FROM CS_T1 WHERE INDEX_ID IN 
    (select index_id from inserted where substring(CMP_PROCESS_NOTE,1,5)='error')
    You should also only execute the delete if CMP_PROCESS_NOTE has been updated.
    Put all your deletes inside a IF...BEGIN...END.

    Code:
    if updated(CMP_PROCESS_NOTE)
    begin
    DELETE  FROM CS_T1 WHERE INDEX_ID IN 
    (select index_id from inserted where substring(CMP_PROCESS_NOTE,1,5)='error')
    ....
    ....
    ....
    end

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Another thing that struck me - is your db well designed? Often tables suffixed 1 to n are a sign of a possible design & maintainability issue.

    Also - if these deletes do not need to be cascaded immediately, and\ or speed is a real issue for action on your dbo.CS_INDEX table then you could have the trigger simply populate another table with the relevent ids and have a job lick them up. Running the deletes synchronously like this will slow down your updates. Still - that depends on your business requirements.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
     
    if updated(CMP_PROCESS_NOTE) begin DELETE FROM CS_T1 WHERE INDEX_ID IN (select index_id from inserted where substring(CMP_PROCESS_NOTE,1,5)='error') .... .... .... end
    I would use a like (assuming CMP_PROCESS_NOTE is indexed) and a join. IN can be inefficient.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by pootle flump
    I would use a like (assuming CMP_PROCESS_NOTE is indexed) and a join. IN can be inefficient.
    I agree.

    It will then look like this

    Code:
    if updated(CMP_PROCESS_NOTE)
    begin
    DELETE FROM CS_T1 
    FROM CS_T1 t inner join inserted i on c.index_id=i.index_id where CMP_PROCESS_NOTE like 'error%'
    ....
    ....
    ....
    end

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yay!

    Although you have a spare FROM in there
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2002
    Posts
    42
    Thanks for the help!

  11. #11
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by pootle flump
    Yay!

    Although you have a spare FROM in there
    Not according to BOL

  12. #12
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Quote Originally Posted by kaffenils
    Not according to BOL
    Oh yes, you have
    Code:
    DELETE FROM CS_T1 
    FROM CS_T1 t inner join inserted i on c.index_id=i.index_id where CMP_PROCESS_NOTE like 'error%'
    Could be written as
    Code:
    DELETE CS_T1 
    FROM CS_T1 t inner join inserted i on c.index_id=i.index_id where CMP_PROCESS_NOTE like 'error%'
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  13. #13
    Join Date
    Nov 2005
    Posts
    122
    Hi roac,

    I haven't tried your suggestion, so I'm not 100% sure that it won't work, but cut'n'paste from BOL says:

    D. Using DELETE based on a subquery and using the Transact-SQL extension
    The following example shows the Transact-SQL extension used to delete records from a base table that is based on a join or correlated subquery. The first DELETE statement shows the SQL-2003-compatible subquery solution, and the second DELETE statement shows the Transact-SQL extension. Both queries remove rows from the SalesPersonQuotaHistory table based on the year-to-date sales stored in the SalesPerson table.

    Copy Code
    -- SQL-2003 Standard subquery

    USE AdventureWorks;
    GO
    DELETE FROM Sales.SalesPersonQuotaHistory
    WHERE SalesPersonID IN
    (SELECT SalesPersonID
    FROM Sales.SalesPerson
    WHERE SalesYTD > 2500000.00);
    GO

    Copy Code
    -- Transact-SQL extension
    USE AdventureWorks;
    GO
    DELETE FROM Sales.SalesPersonQuotaHistory
    FROM Sales.SalesPersonQuotaHistory AS spqh
    INNER JOIN Sales.SalesPerson AS sp
    ON spqh.SalesPersonID = sp.SalesPersonID
    WHERE sp.SalesYTD > 2500000.00;
    GO
    Are you sure you're not thinking about the UPDATE statement?

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by kaffenils
    Not according to BOL
    You are quite correct - apologies I have never seen that before. The first FROM is optional - my mistake was I thought it would be invalid.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by pootle flump
    You are quite correct - apologies I have never seen that before. The first FROM is optional - my mistake was I thought it would be invalid.
    Both FROM are required. If you leave one out you get a 'Incorrent syntax' error.

    Try for yourself if you don't believe me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •