dml Trigger and Idenitity Columns

[ollie2308]

I have 2 "audit" tables that I would like to be populated via a trigger whenever data is Inserted, Updated or deleted. For each Audit_Event, there will be 1 or more Audit_Value records. The parent Audit_Event table uses an Identity column as it's PK and links to the Audit_Value table via this column.

Here are the table defs

CREATE TABLE x_Audit_Event
(
Audit_Event_Id numeric(25, 0) IDENTITY(1, 1) NOT NULL,
Event_Date datetime NULL,
Event_Type varchar(20) NOT NULL,
Table_Name varchar(30) NOT NULL,
PK_Column_Name varchar(50) NOT NULL,
PK_Value numeric(25, 0) NOT NULL,
Userid varchar(30) NOT NULL,
Comment_Text varchar(2000) NULL,
CONSTRAINT XPKx_Audit_Event PRIMARY KEY CLUSTERED (Audit_Event_Id ASC) ON GEDIS_Audit
)
ON GEDIS_Audit;

CREATE TABLE x_Audit_Value
(
Audit_Value_Id numeric(25, 0) IDENTITY(1, 1) NOT NULL,
Audit_Event_Id numeric(25, 0) NOT NULL,
Column_Name varchar(30) NOT NULL,
Column_Type varchar(20) NULL,
Old_Value varchar(2000) NULL,
New_Value varchar(2000) NULL,
CONSTRAINT XPKx_Audit_Value PRIMARY KEY CLUSTERED (Audit_Value_Id ASC) ON GEDIS_Audit,
CONSTRAINT xR_52 FOREIGN KEY (Audit_Event_Id) REFERENCES dbo.x_Audit_Event ( Audit_Event_Id )
)
ON GEDIS_Audit;



and the insert statements from the trigger (simplified)

---- if update

INSERT into x_Audit_Event(Event_Date, Event_Type, Table_Name, PK_Column_Name, PK_Value, Userid)
select getdate(), 'U', 'End_Item_Event', 'End_Item_Event_Id',
inserted.End_Item_Event_Id, system_user from inserted

Insert into x_Audit_Value(Audit_Event_id, Column_Name, Column_Type, Old_Value, New_Value)
select SCOPE_IDENTITY(), 'End_Item_Inventory_Id', 'numeric' , deleted.End_Item_Inventory_Id, inserted.End_Item_Inventory_Id
from inserted, deleted
where inserted.End_Item_Event_Id = deleted.End_Item_Event_Id


When updating multiple records all the child records in the Audit_Value table are linked to the last Audit_Event record.

Audit_evnet data

Audit_Event_Id Event_Date Event_Type Table_Name PK_Column_Name PK_Value Userid Comment_Text
904 10/3/2009 11:23:16 AM U End_Item_Event End_Item_Event_Id 3 me
905 10/3/2009 11:23:16 AM U End_Item_Event End_Item_Event_Id 9261452 me
906 10/3/2009 11:23:16 AM U End_Item_Event End_Item_Event_Id 2 me
907 10/3/2009 11:23:16 AM U End_Item_Event End_Item_Event_Id 1 me
908 10/3/2009 11:23:16 AM U End_Item_Event End_Item_Event_Id 9261451 me

Audit_Vale data

Audit_Value_Id Audit_Event_Id Column_Name Column_Type Old_Value New_Value
1 908 Approval_Chain varchar x
2 908 Approval_Chain varchar x
3 908 Approval_Chain varchar x
4 908 Approval_Chain varchar x
5 908 Approval_Chain varchar x
6 908 Approval_Chain varchar x

So my question is How do I correctly reference the identity value from the parent audit_event table when inserting into the child Audit_Value table ?

Thanks