December 12, 2012 at 8:19 am
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
December 12, 2012 at 8:30 am
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
December 12, 2012 at 8:37 am
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
December 12, 2012 at 8:50 am
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?
December 12, 2012 at 8:54 am
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
December 12, 2012 at 8:57 am
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
December 12, 2012 at 9:00 am
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
December 12, 2012 at 9:05 am
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
December 12, 2012 at 9:07 am
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.
December 12, 2012 at 9:12 am
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
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
December 12, 2012 at 9:17 am
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
December 13, 2012 at 3:01 am
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
December 17, 2012 at 6:59 am
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
December 18, 2012 at 7:19 am
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