Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Delete trigger Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2012 8:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 2,840, Visits: 2,858
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 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
Post #1395693
Posted Wednesday, December 12, 2012 8:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:34 PM
Points: 12,755, Visits: 31,122
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1395703
Posted Wednesday, December 12, 2012 8:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 2,840, Visits: 2,858
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
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
Post #1395706
Posted Wednesday, December 12, 2012 8:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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
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

Post #1395718
Posted Wednesday, December 12, 2012 8:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 2,840, Visits: 2,858
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 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
Post #1395726
Posted Wednesday, December 12, 2012 8:57 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 10:04 AM
Points: 15,442, Visits: 9,590
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
Post #1395729
Posted Wednesday, December 12, 2012 9:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 2,840, Visits: 2,858
@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 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
Post #1395733
Posted Wednesday, December 12, 2012 9:05 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 10:04 AM
Points: 15,442, Visits: 9,590
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
Post #1395739
Posted Wednesday, December 12, 2012 9:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:15 PM
Points: 5,473, Visits: 23,560
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
Post #1395740
Posted Wednesday, December 12, 2012 9:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 2,840, Visits: 2,858
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 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
Post #1395746
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse