Delete trigger

  • I'm not sure what is going on here - if my brain has gone on Christmas holiday early or what, but I'm having issues with an apparently simple task.

    When data is deleted from a table I want to log it (for specific criteria only) into a table which can then be examined in case of issues at a later date. Nothing new here - so I thought a delete trigger would be in order.

    This code as the trigger doesn't do the trick :-

    INSERT INTO utils..TassignmentLogging

    SELECT

    deleted.* ,

    HOST_NAME() HostName ,

    GETDATE() TheDate ,

    DB_NAME() TheDb ,

    APP_NAME() UserApp

    FROM

    deleted

    INNER JOIN dbo.TTASK

    ON deleted.TASKID = dbo.TTASK.UNIQUEID

    WHERE

    ttask.id = 23

    I have proved that the query for the select is correct and would produce a record.

    On running this in Test - and deleted a record that would satisfy the criteria - I don't get any rows in my target table.

    Any ideas anyone?

    (edited for typos)

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • Stuart what is the relevance of this WHERE statement coming from?

    WHERE ttask.id = 23

    are you only capturing queries that match a certain task type?

    it looks to me like that would be the point of failure to me; the ID looks like it's a copy paste from another query that should not be part of the trigger, maybe?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (12/12/2012)


    Stuart what is the relevance of this WHERE statement coming from?

    WHERE ttask.id = 23

    are you only capturing queries that match a certain task type?

    Correct - at the moment we have several process that update the system in question either via SQL jobs, the database application or 3rd party software. When this specific task type is affected it has the potential to screw the system for the user. So what I'm trying to do is at least find out what is doing it before I can explain to someone the errors of their ways 😀

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • Is it that the trigger is fireing after the delete therefore the deleted row doesnt exist any more in TTASK, and a before delete trigger will be the way to go?

    That is assuming the trigger is on the TTASK table?

  • Hi Anthony,

    No the deleted table is not the ttask table.

    In trying to trouble shoot this myself I can get values for fields in the deleted table and put those into a table.

    However I can not be certain that only one row will be affected so can't read deleted table into a series of local variables and then write them into the audit table

    ttask does not have any deletions in this process

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • Is the actual delete rolled back anywhere in your script?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • @GSquared

    No rollbacks or fails for that matter - as far as I can see.

    The trigger is the only one on the table and is as simple as script above

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • In that case, I'd need table definitions, sample data (insert statements, please), and the full create script for the trigger.

    Once I can reproduce the error, it should be simple the solve.

    Without that, I'm left guessing a bit. Possibly the join is on the wrong columns. That would be my next guess, based on the column names. But guessing something, then guessing something else, till we get it, is where we're at right now, till we can reproduce the issue.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Just a wild idea, but when writing triggers I tend to be rather specific in specifying when they fire for example:

    Create trigger orders_update_inventory on orders

    for update

    As I said a wild idea, - but you might want to give it a test run.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Hi Ron,

    full script is :-

    CREATE TRIGGER [dbo].[TASSIGNMENT_DTRIG] ON [dbo].[TASSIGNMENT] FOR DELETE AS

    SET NOCOUNT ON

    INSERT INTO utils..TassignmentLogging

    SELECT

    deleted.* ,

    HOST_NAME() HostName ,

    GETDATE() TheDate ,

    DB_NAME() TheDb ,

    APP_NAME() UserApp

    FROM

    deleted

    INNER JOIN dbo.TTASK

    ON deleted.TASKID = dbo.TTASK.UNIQUEID

    WHERE

    ttask.id = 23

    Unless I've mis-understood you

    @GSquared

    Thanks for the offer - got a few things to look at before I go today - most likely won't be posting that until tomorrow.

    Will be free to answer any general queries before then though

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • bitbucket-25253 (12/12/2012)


    Just a wild idea, but when writing triggers I tend to be rather specific in specifying when they fire for example:

    Create trigger orders_update_inventory on orders

    for update

    As I said a wild idea, - but you might want to give it a test run.

    Won't matter. Default is "for/after" (which are the same thing). Only way it changes is if you specify "instead of".

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • The overnight system gremlins were a bit busy last night - so this is having to go on hold.

    As and when I have anything futher, info, scripts etc - I'll post them here

    Thanks

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • An update:-

    All is now working and the code is running 100% OK.

    I simplified the trigger as after research I found that I didn't need the join to ttask, the id I was looking for was in the deleted data so could directly filter on that.

    Don't know why this should make the difference, but I am now able to log the data I need to.

    Thanks to everyone who chipped in.

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • Glad it worked out.

    Often, all it takes is approaching the problem from a slightly different direction.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply