SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Delete trigger


Delete trigger

Author
Message
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7331 Visits: 4817
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 Smart way to ask a questionThere 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
Lowell
Lowell
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69870 Visits: 40918
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!
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7331 Visits: 4817
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 :-D

-------------------------------Posting Data Etiquette - Jeff Moden Smart way to ask a questionThere 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
anthony.green
anthony.green
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23736 Visits: 6519
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?



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7331 Visits: 4817
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 Smart way to ask a questionThere 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
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56285 Visits: 9730
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
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7331 Visits: 4817
@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 Smart way to ask a questionThere 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
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56285 Visits: 9730
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
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15371 Visits: 25280
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

Before posting a performance problem please read
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7331 Visits: 4817
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 Smart way to ask a questionThere 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search