April 16, 2009 at 3:51 am
Hi guys,
i have this problem with delete trigger
I am creating an audit trail with the help of the following trigger. If make some update in my base table, my trigger extract information from the deleted table validates it with the base table using the primary key and inserts in the audit table.
But in the case of delete there is no reference in my base table since the row is already deleted from the base table (have to use after delete) and deleted table is a global table. how do I validate if the information retrieved from the deleted table is that of the information deleted from by base table.
Could you please help me out.
Below is the trigger
ALTER Trigger [dbo].[TrgForAudit]
on [dbo].[Department]
After update,delete
as
declare @resultXml varchar(MAX)
declare @tableName varchar(50)
declare @action varchar(20)
declare @user-id varchar(50)
--Set table name
set @tableName = 'Department'
--Set fields
IF Update(LastUpdated)
BEGIN
set @action = 'Update'
--Set UserId value from the table which is modified
--log the updated history in the Change tracker table
insert into Change_Tracker
(UserID,
TransactionID,
DateTime,
Action,
ModifiedXML,
TableName
)
selectins.UserID,
ins.DepartmentID,
getdate(),
@action,
'<'+@tableName+'>' + (select * from deleted del inner join Department d ON del.DepartmentID = d.DepartmentID for xml auto) + '',
@tableName
from inserted ins inner join Department d ON ins.DepartmentID = d.DepartmentID
END
ELSE
BEGIN
set @action = 'Delete'
--Set UserId value from the table which is modified
--log the deleted history in the Change tracker table
insert into Change_Tracker
(UserID,
TransactionID,
DateTime,
Action,
ModifiedXML,
TableName
)
select del.UserID,
del.DepartmentID,
getdate(),
@action,
'<'+@tableName+'>' + (select * from deleted del inner join Department d ON del.DepartmentID = d.DepartmentID for xml auto) + '',
@tableName
from deleted del inner join Department d ON del.DepartmentID = d.DepartmentID
END
April 16, 2009 at 2:33 pm
At a glance I don't see anything wrong with this trigger. The record in the original table is not really gone yet. It is in the middle of a transaction that is deleting it. It will be in deleted. If for some reason in a trigger you determine that you want to cancel the action you can rollback the transaction. 😀
I'm not really sure what you mean by referring to your base table. I assume you are talking the table you are deleting from?
_______________________________________________________________
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/
April 16, 2009 at 2:41 pm
dimp_malhotra (4/16/2009)
how do I validate if the information retrieved from the deleted table is that of the information deleted from by base table.
If you had a DELETE operation then any rows that are in the [deleted] pseudotable were deleted from the base table.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 16, 2009 at 10:42 pm
Thanks a lot for guys for your help...but its still kinda not solved
yes i know it will in the deleted table...but is'nt deleted table a global table... wont it give me concurrency issue if i dont check if the value retrieved from the deleted table is the same as that deleted from my table on which my trigger is fired
This is is where i am getting my problem...i dont know how to solve it..
pls pls help
April 16, 2009 at 11:34 pm
dimp_malhotra (4/16/2009)
Thanks a lot for guys for your help...but its still kinda not solvedyes i know it will in the deleted table...but is'nt deleted table a global table... wont it give me concurrency issue if i dont check if the value retrieved from the deleted table is the same as that deleted from my table on which my trigger is fired
No, this is not how it works.
The inserted and deleted tables are created from the data that your current transaction just wrote into the transaction log. It is not shared with (nor can it be confused with) the data from any other transaction or any other process, including transactions on the same table.
It is 100% concurrency safe.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 17, 2009 at 12:08 am
Are you sure about that.....
This means I do not need to put any inner join while retrieving from inserted and deleted
for example (The way i had put in my trigger)
from inserted ins inner join Department d ON ins.DepartmentID = d.DepartmentID
if thats the case it was great help thanks a lot........
April 17, 2009 at 6:06 am
AFAIK, that is how it works. You normally only need to inner join the inserted table with the base table when you need to find the corresponding records in the base table.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 19, 2009 at 1:11 am
RBarryYoung (4/16/2009)
The inserted and deleted tables are created from the data that your current transaction just wrote into the transaction log....
Hey Barry,
That used to be so; howver SQL 2K5 uses row-level versioning (RLV) for the inserted and deleted pseudo-tables on AFTER triggers. INSTEAD OF triggers do not use RLV.
Cheers,
Paul
April 19, 2009 at 6:39 am
Can you point me to an MS reference for that? I am getting conflicting info on that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 19, 2009 at 7:03 am
RBarryYoung (4/19/2009)
Can you point me to an MS reference for that? I am getting conflicting info on that.
Kalen wrote a good article for SQL Mag - but that's subscriber only.
So, see this MSDN entry
MSDN
Other features that affect the size of the version store are triggers and MARS. In SQL Server 2005, triggers use row versions to generate inserted and deleted rows, instead of using scanning the log records.
April 19, 2009 at 11:05 am
Still a little puzzled. I thought that you only got row-versioning if you turned Snapshots or RCSI on?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 19, 2009 at 3:29 pm
RBarryYoung (4/19/2009)
Still a little puzzled. I thought that you only got row-versioning if you turned Snapshots or RCSI on?
I know, it's a common thing. SS2K5 uses row-versioning internally for AFTER triggers, MARS, and on-line index rebuilds - regardless of the database's snapshot isolation setting. See 'sys.dm_tran_active_snapshot_database_transactions' in BOL:
To quote Peter Sellars (not Michael Caine) "not a lot of people know that" - MS should make more of it.
You can see RLV in action by querying the sys.dm_tran_active_snapshot_database_transactions dynamic view when a trigger, MARS, or on-line index rebuild is active.
Cheers,
Paul
April 20, 2009 at 10:54 pm
ok i am really lost.....am i on the right track or is there something that needs to done on my trigger to avoid concurrency......
April 20, 2009 at 11:00 pm
dimp_malhotra (4/20/2009)
ok i am really lost.....am i on the right track or is there something that needs to done on my trigger to avoid concurrency......
Go with Barry's original advice. We're just having a discussion about stuff that doesn't affect your answer.
Cheers,
Paul
April 21, 2009 at 12:55 am
Thanks a million............it was great help 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply