Trigger question
-
Hello
I've a trigger question that I would like to share. I have been asked to implement an audit trigger on two tables. The tables in question are a header table and corresponding line items, named ticket_info and ticket_commodity respectively.
The 'source' tables contain information about tickets entered into our system, and can come from a variety of sources, some automated, (message queue), some entered manually through the front end in our application.
The reason for this is two fold.
1. We will have an accurate representation of the stages a ticket progresses through, for 'traditional' audit purposes, reconciliation etc.
2. We can use the information in the audit tables to generate posts to our financials application.
To this end we are designing a process to accomplish these two disparate goals. Number 1 is straight forward and is working a treat (in test), but 2 is proving more elusive.
The strategy was the the audit tables would be a straight reflection of the source tables, with two additional columns. One would be a GUID column, datatype uniqueIdentifier, the other a datetime column call audit_created_date. Both would be not null and have the appropriate defaults.
The lines we need to generate to the financials system would be stored in yet another table called Atomic_lines. The reasoning for this is that one commodity line can post 2 to n lines in the financials system, depending on, amongst other things, the type of ticket the commodity line belongs to, the material the commodity is etc. (one line per nominal affected).
I thought we could write a view to give a de-normalized view across the tickets header and line tables, capturing the latest records only, and then by copying the GUID column to the atomic_lines table, anti-join on the view to work out which records in the audit view we need to process to create the records in atomic_lines. New updates to the ticket would generate new lines in the audit table which the view will pick up due to the new GUID.
I wrote a test script to generate some small tables and the necessary triggers and views etc., which worked well as a proof of concept. (I'll post this script at the end of this post so you can see what I'm wittering on about).
Then we come to the real world
In a test system I generated the audit table and trigger for the Header table and created a ticket manually. This resulted in an initial insert into my audit table, followed by 5 separate updates!This meant the last two lines contained the same timestamp in the audit_created_date column. So my strategy for getting the latest line is not going to work, as I cannot guarantee that I can get the last record. Using an identity column and using that in the MAX clause (see below) is a no-no, as the poorly written app is scattered with references to @@identity, both in SPs and the client side code. (Written incidentally before I joined in Java). I tried it anyway and as expected broke it.
So I have two questions: Can I use some strategy to identify the last row inserted into the audit table other than a datetime column, without using an identity field?
Or can I safely search and replace all calls to @@identity with Scope_Identity() and it should all just work?
Thanks for reading this far, here’s my test script:
If Object_id('Tick_info') > 0
Drop Table Tick_info
Go
Create Table Tick_info (
Ticket_txn_id Int Not Null Identity( 1 , 1 ) Primary Key,
Depot_no Varchar(10) Not Null,
Ticket_type Char(1) Not Null Default 'I',
Party_account_no Varchar(10) Not Null,
Created_dt Datetime Default Getdate())
Go
If Object_id('Tick_comm') > 0
Drop Table Tick_comm
Go
Create Table Tick_comm (
Comm_id Int Not Null Identity( 1 , 1 ) Primary Key,
Ticket_txn_id Int Not Null,
Grade Varchar(10) Not Null,
Prov_comm_value Money Not Null Default 0,
Prov_tonnes Decimal(10,4) Not Null Default 0,
Created_dt Datetime Default Getdate())
Go
If Object_id('Tick_info_history') > 0
Drop Table Tick_info_history
Go
Create Table Tick_info_history (
Ticket_txn_id Int Not Null,
Depot_no Varchar(10) Not Null,
Ticket_type Char(1) Not Null,
Party_account_no Varchar(10) Not Null,
Created_dt Datetime Not Null,
Guid Uniqueidentifier Not Null Default Newid() Unique,
History_created_dt Datetime Not Null Default Getdate())
Go
Create Index Ix_cl_guid On Tick_info_history (
Guid)
Go
Alter Table Tick_info_history
Add Constraint Pk_tick_info_history Primary Key( Guid )
Go
If Object_id('Tick_comm_history') > 0
Drop Table Tick_comm_history
Go
Create Table Tick_comm_history (
Comm_id Int Not Null,
Ticket_txn_id Int Not Null,
Grade Varchar(10) Not Null,
Prov_comm_value Money Not Null,
Prov_tonnes Decimal(10,4) Not Null,
Created_dt Datetime Not Null,
Guid Uniqueidentifier Not Null Default Newid() Unique,
History_created_dt Datetime Not Null Default Getdate())
Go
Create Index Ix_cl_guid On Tick_comm_history (
Guid)
Go
Alter Table Tick_comm_history
Add Constraint Pk_tick_comm_history Primary Key( Guid )
Go
Create Index [ix_ncl_history_created_dt] On [dbo].[tick_info_history] (
[ticket_txn_id],
[history_created_dt])
Go
Create Index [ix_ncl_history_created_dt] On [dbo].[tick_comm_history] (
[ticket_txn_id],
[history_created_dt])
Go
If Object_id('Atomic_lines') > 0
Drop Table Atomic_lines
Go
Create Table Atomic_lines (
Al_id Int Not Null Identity( 1 , 1 ),
Ticket_txn_id Int Not Null,
Comm_id Int Not Null,
Depot_no Varchar(10) Not Null,
Party_account_no Varchar(10) Not Null,
Ticket_type Char(1) Not Null,
Grade Varchar(10) Not Null,
Prov_comm_value Money Not Null,
Prov_tonnes Decimal(10,4) Not Null,
Info_created_dt Datetime Not Null,
Comm_created_dt Datetime Not Null,
Info_guid Uniqueidentifier Not Null,
Comm_guid Uniqueidentifier Not Null)
Go
Create Clustered Index [ix_cl_guids] On [dbo].[atomic_lines] (
[info_guid],
[comm_guid])
Go
Alter Table Atomic_lines
Add Constraint Pk_atomic_lines Primary Key( Al_id )
Go
Create Trigger Tr_tick_info
On Tick_info
For Insert,Update,Delete
As
Set Nocount On
Insert Into Tick_info_history
(Ticket_txn_id,
Depot_no,
Ticket_type,
Party_account_no,
Created_dt)
Select Ticket_txn_id,
Depot_no,
Ticket_type,
Party_account_no,
Created_dt
From Inserted
Set Nocount Off
Go
Create Trigger Tr_tick_comm
On Tick_comm
For Insert,Update,Delete
As
Set Nocount On
Insert Into Tick_comm_history
(Comm_id,
Ticket_txn_id,
Grade,
Prov_comm_value,
Prov_tonnes,
Created_dt)
Select Comm_id,
Ticket_txn_id,
Grade,
Prov_comm_value,
Prov_tonnes,
Created_dt
From Inserted
Set Nocount Off
Go
If Object_id('Ticket_History_vw') > 0
Drop View Ticket_history_vw
Go
Create View Ticket_history_vw
As
Select A.Ticket_txn_id,
B.Comm_id,
A.Depot_no,
A.Ticket_type,
A.Party_account_no,
A.Created_dt Info_created_dt,
A.Guid Info_guid,
A.History_created_dt Info_history_created_dt,
B.Grade,
B.Prov_comm_value,
B.Prov_tonnes,
B.Created_dt Comm_created_dt,
B.Guid Comm_guid,
B.History_created_dt Comm_history_created_dt
From (Select At1.*
From Tick_info_history At1
Where At1.History_created_dt = (Select Max(At2.History_created_dt)
From Tick_info_history At2
Where At1.Ticket_txn_id = At2.Ticket_txn_id)) A
Inner Join (Select At1.*
From Tick_comm_history At1
Where At1.History_created_dt = (Select Max(At2.History_created_dt)
From Tick_comm_history At2
Where At1.Ticket_txn_id = At2.Ticket_txn_id)) B
On A.Ticket_txn_id = B.Ticket_txn_id
Go
------------------------------------------------------------------------------
--generate some dummy data
Insert Into Tick_info
(Depot_no,
Ticket_type,
Party_account_no)
Select Top 1000 'DFERSAL' Depot_no,
'I' Ticket_type,
'LPart001' Party_account_no
From Master..Sysobjects S
Cross Join Master..Sysobjects S2
Insert Into Tick_comm
(Ticket_txn_id,
Grade,
Prov_tonnes,
Prov_comm_value)
Select I.Ticket_txn_id,
'7B' Grade,
10 Prov_tonnes,
100 Prov_comm_value
From Tick_info I
--------------------------
Set Rowcount 990
Update Tick_info
Set Depot_no = 'DFERBLA'
Update Tick_comm
Set Grade = 'A211'
Set Rowcount 0
Select Count(* )
From Ticket_history_vw
Insert Into Atomic_lines
(Ticket_txn_id,
Comm_id,
Depot_no,
Ticket_type,
Party_account_no,
Grade,
Prov_comm_value,
Prov_tonnes,
Info_created_dt,
Comm_created_dt,
Info_guid,
Comm_guid)
Select Top 990 Ticket_txn_id,
Comm_id,
Depot_no,
Ticket_type,
Party_account_no,
Grade,
Prov_comm_value,
Prov_tonnes,
Info_history_created_dt,
Comm_history_created_dt,
Info_guid,
Comm_guid
From Ticket_history_vw
Order By Ticket_txn_id
Select @@rowcount
--------------------------------------------------------------------------------------------
--vars for timing
Declare @st Datetime,
@end Datetime
Select @st = Getdate()
--what left to process?
Select *
From Ticket_history_vw V
Left Outer Join Atomic_lines A
On V.Info_guid = A.Info_guid
And V.Comm_guid = A.Comm_guid
Where A.Info_guid Is Null
And A.Comm_guid Is Null
Order By V.Comm_id
Select @@rowcount
Set @end = Getdate()
Select Cast(Datediff(Ms,@st,@end) As Varchar) + ' milliseconds'
-
Have a look at the data type "TIMESTAMP". Functionally, it is similar to IDENTITY in that is automatically increments. It is a binary number starting at 0 and increments every time the record is saved. The value of the timestamp is unique thoughout the database - not just the individual table. Since it increments every time a record is saved, your logic of getting the MAX value will work quite well.
One thing to note is the despite its name, the TIMESTAMP datatype does not have antyhing to do with the time (or date). If you need to know the date / time that a record was inserted/updated, you will still need to use a datetime/smalldatetime column.
-
/*
Excellent. I tried to go back to the source tables as they always contain the
latest record, but that goes against the grain of what we are trying to do.
Your sugestion is spot on, and once again I learn something new.
I followed BOLs advice and used the rowversion synonym, which until now I'd never heard of.
Cheers!
Dave J
*/
----------------------------------------------------
--table definition
IF OBJECT_ID('Tick_info_history') > 0
DROP TABLE TICK_INFO_HISTORY
GO
CREATE TABLE TICK_INFO_HISTORY (
TICKET_TXN_ID INT NOT NULL,
DEPOT_NO VARCHAR(10) NOT NULL,
TICKET_TYPE CHAR(1) NOT NULL,
PARTY_ACCOUNT_NO VARCHAR(10) NOT NULL,
CREATED_DT DATETIME NOT NULL,
GUID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() UNIQUE,
TS ROWVERSION NOT NULL,
HISTORY_CREATED_DT DATETIME NOT NULL DEFAULT GETDATE())
GO
IF OBJECT_ID('Tick_comm_history') > 0
DROP TABLE TICK_COMM_HISTORY
GO
CREATE TABLE TICK_COMM_HISTORY (
COMM_SEQ_ID INT NOT NULL,
TICKET_TXN_ID INT NOT NULL,
GRADE VARCHAR(10) NOT NULL,
PROV_COMM_VALUE MONEY NOT NULL,
PROV_TONNES DECIMAL(10,4) NOT NULL,
CREATED_DT DATETIME NOT NULL,
GUID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() UNIQUE,
TS ROWVERSION NOT NULL,
HISTORY_CREATED_DT DATETIME NOT NULL DEFAULT GETDATE())
GO
----------------------------------------------------
--view definition
IF OBJECT_ID('Ticket_History3_vw') > 0
DROP VIEW TICKET_HISTORY3_VW
GO
CREATE VIEW TICKET_HISTORY3_VW
AS
SELECT A.TICKET_TXN_ID,
B.COMM_SEQ_ID,
A.DEPOT_NO,
A.TICKET_TYPE,
A.PARTY_ACCOUNT_NO,
A.CREATED_DT INFO_CREATED_DT,
A.GUID INFO_GUID,
A.HISTORY_CREATED_DT INFO_HISTORY_CREATED_DT,
B.GRADE,
B.PROV_COMM_VALUE,
B.PROV_TONNES,
B.CREATED_DT COMM_CREATED_DT,
B.GUID COMM_GUID,
B.HISTORY_CREATED_DT COMM_HISTORY_CREATED_DT
FROM (SELECT AT1.*
FROM TICK_INFO_HISTORY AT1
WHERE AT1.TS = (SELECT MAX(AT2.TS)
FROM TICK_INFO_HISTORY AT2
WHERE AT1.TICKET_TXN_ID = AT2.TICKET_TXN_ID)) A
INNER JOIN (SELECT AT1.*
FROM TICK_COMM_HISTORY AT1
WHERE AT1.TS = (SELECT MAX(AT2.TS)
FROM TICK_COMM_HISTORY AT2
WHERE AT1.TICKET_TXN_ID = AT2.TICKET_TXN_ID)) B
ON A.TICKET_TXN_ID = B.TICKET_TXN_ID
GO
----------------------------------------------------
--usage
DECLARE @st DATETIME,
@end DATETIME
SELECT @st = GETDATE()
SELECT *
FROM TICKET_HISTORY3_VW V
LEFT OUTER JOIN ATOMIC_LINES A
ON V.INFO_GUID = A.INFO_GUID
AND V.COMM_GUID = A.COMM_GUID
WHERE A.AL_ID IS NULL
ORDER BY V.TICKET_TXN_ID,
V.COMM_SEQ_ID
SELECT @@ROWCOUNT
SET @end = GETDATE()
SELECT CAST(DATEDIFF(MS,@st,@end) AS VARCHAR) + ' milliseconds'
-
USE
IDENT_CURRENT('TableName')
instead of @@IDENTITY
I had this problem with triggers that create records breaking all tables that used @@IDENTITY. So on all the tables that contained these triggers, I replaced @@IDENTITY with IDENT_CURRENT(. . . ) and all is now working smoothly.
WARNING . . If you are creating records in a trigger from a stored proc that uses @@IDENTITY to return the ID of the record . . .
THIS VALUE WILL BE THE @@IDENTITY OF THE TABLE INSERTED TO BY THE TRIGGER, NOT THAT OF THE ORIGINAL TABLE UPDATED!!!
Alan
-
Scope_Identity is usually a better alternative than @@Identity.
-
Correct, I'm well aware of the problem with @@identity, in fact I was 'stung' by it at the last place. Andy is right, the Scope_Identity is better, as Ident_current('tbl') returns the last value in any session. happycat59s suggestion is working a treat and I'm now turning my test script into a working example.
Dave J
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply