January 8, 2014 at 3:35 pm
I know I've just over looked something with my After Trigger but I can't see what it is (Can't see the forest for the trees) and I hope one of you SQLGurus can shed some light on this.
The goal is to capture the data from MYTABLE on INSERT, UPDATE or DELETE actions and do it in a passive way. I don't want to check on some value and then possibly change some value being inserted, updated or deleted. I just want to get a copy of what was DELETED, UPDATE or INSERTED.
The below is the general structure of the Aftter Trigger I am using now. The problem is that the trigger is executing the 2nd and 3rd 'ELSE IF' blocks and never executing the first one. I was trying to design it so that only 1 of the 3 would execute depending on what had occured, INSERT, UPDATE or DELETE; and yet the INSERT and DELETE blocks always execute and the UPDATE block has yet to execute and I know because one of the values I plug into the audit table is the trigger type (I, U or D ).
What am I missing? Perhaps an AFTER trigger is not what I should be using?
Thanks
CREATE TRIGGER [dbo].[MAYTABLE_A_IUD_WD] ON [dbo].[MYTABLE] AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM INSERTED I JOIN DELETED D ON I.hMy = D.hMy )
BEGIN
INSERT INTO dbo.AUDIT_TABLE ( Column1, Column2, Column3,....ColumnN )
SELECT I.Col1, I.Col2, I.Col3,.....I.ColumnN
FROM INSERTED I JOIN DELETED D ON I.PrimaryKey = D.PrimaryKey
END
ELSE IF EXISTS (SELECT 1 FROM INSERTED)
BEGIN
INSERT INTO dbo.AUDIT_TABLE ( Column1, Column2, Column3,....ColumnN )
SELECT I.Col1, I.Col2, I.Col3,.....I.ColumnN
FROM INSERTED I
END
ELSE IF EXISTS (SELECT 1 FROM DELETED)
BEGIN
INSERT INTO dbo.AUDIT_TABLE ( Column1, Column2, Column3,....ColumnN )
SELECT I.Col1, I.Col2, I.Col3,.....I.ColumnN
FROM DELETED D
END
END
Kindest Regards,
Just say No to Facebook!January 8, 2014 at 3:45 pm
Your join in the condition is different than the insert statement.
IF EXISTS (SELECT 1 FROM INSERTED I JOIN DELETED D ON I.hMy = D.hMy )
BEGIN
INSERT INTO dbo.AUDIT_TABLE ( Column1, Column2, Column3,....ColumnN )
SELECT I.Col1, I.Col2, I.Col3,.....I.ColumnN
FROM INSERTED I JOIN DELETED D ON I.PrimaryKey = D.PrimaryKey
END
You are checking for rows that exist in both inserted and deleted with matching hMy but your insert statement is joining on PrimaryKey.
Personally I have never been a big fan of inserting the inserted data into an audit table. That just makes an exact duplicate of the data in the table. In these type of situations I only create audit history on update or delete. You can ALWAYS get the current data from the actual base table without resorting to the audit table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 8, 2014 at 3:48 pm
I don't really see anything wrong with your logic.
Have you verified that there actually are updates being performed? Are there any other triggers on this table? Could the update transactions be failing or getting rolled back? Could there be a duplicate key violation on the audit table?
However, I prefer to do this sort of audit with a separate trigger for each action. It makes each trigger simpler to code and test, since you do not need any logic to test for what action is being performed. It probably performs better also.
January 8, 2014 at 4:11 pm
I'm also failing to see the point of this trigger.
If I insert a row you copy it to your audit.
If I update the row you copy the new row to the audit
Why do you need to copy it again if it is deleted? You already have it in the audit?
This whole trigger could just be reduced to a simple one line without losing any information.
INSERT INTO dbo.AUDIT_TABLE ( Column1, Column2, Column3,....ColumnN )
SELECT I.Col1, I.Col2, I.Col3,.....I.ColumnN
FROM INSERTED I
Now, if you had some other columns in your audit table, such as who changed the rows, when they changed them and whether it was an update to existing , and insert of new or a deletion...then you might want more logic, but probably not.
INSERT INTO dbo.AUDIT_TABLE ( AuditType, AuditUser, AuditDate, Column1, Column2, Column3,....ColumnN )
SELECT
CASE
WHEN D.PrimaryKey IS NULL THEN 'Insert'
WHEN I.PrimaryKey IS NULL THEN 'Delete'
ELSE 'Update'
END,
ORIGINAL_LOGIN( ),
GETDATE(),
ISNULL(D.Col1,I.Col1), ISNULL(D.Col2,I.Col2), ....
FROM INSERTED I
FULL OUTER JOIN DELETED D
ON D.PrimaryKey = I.PrimaryKey
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 18, 2014 at 3:08 pm
Sean,
The difference in the JOINS was a typo on my part. It should have read :
CREATE TRIGGER [dbo].[MAYTABLE_A_IUD_WD] ON [dbo].[MYTABLE] AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM INSERTED I JOIN DELETED D ON I.PrimaryKey = D.PrimaryKey )
BEGIN
INSERT INTO dbo.AUDIT_TABLE ( Column1, Column2, Column3,....ColumnN )
SELECT I.Col1, I.Col2, I.Col3,.....I.ColumnN
FROM INSERTED I JOIN DELETED D ON I.PrimaryKey = D.PrimaryKey
END
ELSE IF EXISTS (SELECT 1 FROM INSERTED)
BEGIN
INSERT INTO dbo.AUDIT_TABLE ( Column1, Column2, Column3,....ColumnN )
SELECT I.Col1, I.Col2, I.Col3,.....I.ColumnN
FROM INSERTED I
END
ELSE IF EXISTS (SELECT 1 FROM DELETED)
BEGIN
INSERT INTO dbo.AUDIT_TABLE ( Column1, Column2, Column3,....ColumnN )
SELECT I.Col1, I.Col2, I.Col3,.....I.ColumnN
FROM DELETED D
END
END
Personally I have never been a big fan of inserting the inserted data into an audit table. That just makes an exact duplicate of the data in the table. In these type of situations I only create audit history on update or delete. You can ALWAYS get the current data from the actual base table without resorting to the audit table.
You are %100 correct. In our case we are trying to capture the WHO and WHEN as well as the WHAT and because i can't alter the structure of teh table (I can't add any columns) I'm capturing that info from the AFTER trigger. If there is a better way to do that which does not requiring modifying the tabloes structure please let me know.
Thanks
Kindest Regards,
Just say No to Facebook!February 18, 2014 at 3:09 pm
Michael,
Using a sepearte trigger just may be the answer in the end. I've kept all the logic in on etrigger in hopes of keeping the code to a single object just because thats easier for me work with.
Thanks
Kindest Regards,
Just say No to Facebook!February 18, 2014 at 3:14 pm
MM,
I am capturing the WHO (From the SQL Login whcih is unique to every user) , the WHEN (GetDate() ) and whether or not it was creation of new data, a change to existing data or deletion of data.
I need to capture this info without making any changes to the structure of teh table so I can't add anby columns to stores this nor can I alter any existing columns and the tabl(s) do not already have columns that store this data, at leaqst not in every case. In order to get that info and not change the table I am duplkicating teh table but with a few extra columns to store the who, the when and the what kind.
Thanks
Kindest Regards,
Just say No to Facebook!February 18, 2014 at 6:33 pm
YSLGuru,
Sorry for being off topic but I'd really like to know who said that quote in your signature.
I live in Bangkok and I'm not sure if you know what is going on here right now. The country's in kind of a mess because of a corrupt government (the wolves) and the middle class is in uprising (the sheep).
Someone needs to get those words out.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 19, 2014 at 12:20 pm
Here's my suggested code for this trigger.
CREATE TRIGGER [dbo].[MAYTABLE_A_IUD_WD]
ON [dbo].[MYTABLE]
AFTER DELETE, INSERT, UPDATE
AS
SET NOCOUNT ON;
DECLARE @action char(6)
IF EXISTS(SELECT 1 FROM inserted)
IF EXISTS(SELECT 1 FROM deleted)
SET @action = 'UPDATE'
ELSE
SET @action = 'INSERT'
ELSE
SET @action = 'DELETE'
IF @action IN ('INSERT', 'UPDATE')
INSERT INTO dbo.AUDIT_TABLE ( Column1, Column2, Column3,....ColumnN )
SELECT I.Col1, I.Col2, I.Col3,.....I.ColumnN
--you could LEFT OUTER JOIN to DELETED here if you needed to
FROM INSERTED I
ELSE
INSERT INTO dbo.AUDIT_TABLE ( Column1, Column2, Column3,....ColumnN )
SELECT D.Col1, D.Col2, D.Col3,.....D.ColumnN
FROM DELETED D
GO
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply