Re: Iterate through the inserted table in an update trigger...
From: Matthew Wells (MWells_at_FirstByte.net)
Date: 12/19/04
- Next message: Rick: "Subtotal"
- Previous message: Murphy: "Re: 2 tier vs 3 tier"
- In reply to: Itzik Ben-Gan: "Re: Iterate through the inserted table in an update trigger..."
- Next in thread: Steve Kass: "Re: Iterate through the inserted table in an update trigger..."
- Reply: Steve Kass: "Re: Iterate through the inserted table in an update trigger..."
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 19 Dec 2004 00:36:27 GMT
Your example worked fine, but my code returns NULL for both old and new
values. Do you know why?
CREATE TRIGGER trffc_AmendmentTracking ON dbo.ffc_AmendmentTracking
FOR INSERT, UPDATE, DELETE
AS
Declare
@HostName varchar(50),
@ChangeType char(1),
@HistoryMasterID int,
@Value_Old nvarchar(255),
@Value_New nvarchar(255),
@RecordLink int
--Determine type of change made to the database.
IF EXISTS (Select * From inserted)
IF EXISTS (Select * From deleted) Set @ChangeType = 'U'
Else Set @ChangeType = 'I'
Else Set @ChangeType = 'D'
Set @HostName = host_name()
IF @HostName is NULL OR @HostName = '' SET @HostName = 'Unknown'
--Insert new unique id and audit transaction into cae_HistoryMaster table.
IF @ChangeType = 'I'
Select @RecordLink = [AmendmentTrackingKey] From Inserted
Else
Select @RecordLink = [AmendmentTrackingKey] From Deleted
Set @HistoryMasterID = 2
IF @ChangeType = 'U' Begin
INSERT INTO ffc_AmendmentTracking_History (HistoryMasterID, RecordLink,
FieldName, Value_Old, Value_New)
Select * from
(Select @HistoryMasterID AS HistoryMasterLink, @RecordLink As RecordLink,
ColName,
case ColName
When 'd.[AmendmentTrackingKey]' Then cast( d.[AmendmentTrackingKey] as
sql_variant )
When 'd.[Lease No]' Then cast( d.[Lease No] as sql_variant )
When 'd.[Amendment]' Then cast( d.[Amendment] as sql_variant )
When 'd.[Amendment Date]' Then cast( d.[Amendment Date] as sql_variant )
When 'd.[Effective Date]' Then cast( d.[Effective Date] as sql_variant )
When 'd.[Amendment Out]' Then cast( d.[Amendment Out] as sql_variant )
When 'd.[Amendment Back]' Then cast( d.[Amendment Back] as sql_variant )
When 'd.[Log In]' Then cast( d.[Log In] as sql_variant )
When 'd.[Log Out]' Then cast( d.[Log Out] as sql_variant )
When 'd.[Amendment To]' Then cast( d.[Amendment To] as sql_variant )
When 'd.[AdditionDate]' Then cast( d.[AdditionDate] as sql_variant )
When 'd.[UserKey]' Then cast( d.[UserKey] as sql_variant )
End as Old,
case ColName
When 'i.AmendmentTrackingKey' Then cast( i.[AmendmentTrackingKey] as
sql_variant )
When 'i.Lease No' Then cast( i.[Lease No] as sql_variant )
When 'i.[Amendment]' Then cast( i.[Amendment] as sql_variant )
When 'i.[Amendment Date]' Then cast( i.[Amendment Date] as sql_variant )
When 'i.[Effective Date]' Then cast( i.[Effective Date] as sql_variant )
When 'i.[Amendment Out]' Then cast( i.[Amendment Out] as sql_variant )
When 'i.[Amendment Back]' Then cast( i.[Amendment Back] as sql_variant )
When 'i.[Log In]' Then cast( i.[Log In] as sql_variant )
When 'i.[Log Out]' Then cast( i.[Log Out] as sql_variant )
When 'i.[Amendment To]' Then cast( i.[Amendment To] as sql_variant )
When 'i.[AdditionDate]' Then cast( i.[AdditionDate] as sql_variant )
When 'i.[UserKey]' Then cast( i.[UserKey] as sql_variant )
End as New
From Deleted as d join Inserted as i On
d.[AmendmentTrackingKey] = i.[AmendmentTrackingKey] cross join (select
N'AmendmentTrackingKey' AS ColName
Union All Select N'Lease No'
Union All Select N'Amendment'
Union All Select N'Amendment Date'
Union All Select N'Effective Date'
Union All Select N'Amendment Out'
Union All Select N'Amendment Back'
Union All Select N'Log In'
Union All Select N'Log Out'
Union All Select N'Amendment To'
Union All Select N'AdditionDate'
Union All Select N'UserKey'
) as cols) as d
Where old <> new
END
"Itzik Ben-Gan" <itzik@REMOVETHIS.SolidQualityLearning.com> wrote in message
news:O6agPI44EHA.3756@TK2MSFTNGP14.phx.gbl...
> If you're after auditing data that actually changed in an UPDATE statement
> (as opposed to columns participating in the SET clause), you can use a
> trigger similar to the following:
>
> create table t1
> (
> keycol int not null identity primary key,
> col1 int not null,
> col2 varchar(10) not null,
> col3 datetime not null
> )
>
> create table audit_t1
> (
> lsn int not null identity primary key,
> username sysname not null default user_name(),
> logdate datetime not null default getdate(),
> keycol int not null,
> colname sysname not null,
> oldval sql_variant null,
> newval sql_variant null
> )
> go
>
> create trigger trg_t1_u_audit_changes on t1 for update
> as
>
> if update(keycol)
> begin
> raiserror('Not allowed to modify PK.', 10, 1)
> rollback
> return
> end
>
> insert into audit_t1(keycol,colname,oldval,newval)
> select *
> from (select
> i.keycol,
> colname,
> case colname
> when 'col1' then cast(d.col1 as sql_variant)
> when 'col2' then cast(d.col2 as sql_variant)
> when 'col3' then cast(d.col3 as sql_variant)
> end as old,
> case colname
> when 'col1' then cast(i.col1 as sql_variant)
> when 'col2' then cast(i.col2 as sql_variant)
> when 'col3' then cast(i.col3 as sql_variant)
> end as new
> from deleted as d
> join inserted as i
> on d.keycol = i.keycol
> cross join (select N'col1' as colname
> union all select N'col2'
> union all select N'col3') as cols) as d
> where old <> new
> go
>
> insert into t1 values(1, 'a', getdate())
> insert into t1 values(2, 'b', getdate())
> insert into t1 values(3, 'c', getdate())
>
> update t1 set col2 = 'x', col1 = 3
>
> select * from audit_t1
>
> lsn, username, logdate, keycol, colname, oldval, newval
> 1 dbo 2004-12-16 17:13:11.507 1 col1 1 3
> 2 dbo 2004-12-16 17:13:11.507 1 col2 a x
> 3 dbo 2004-12-16 17:13:11.507 2 col1 2 3
> 4 dbo 2004-12-16 17:13:11.507 2 col2 b x
> 5 dbo 2004-12-16 17:13:11.507 3 col2 c x
>
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
>
> "Matthew Wells" <MWells@FirstByte.net> wrote in message
> news:4dhwd.749$RH4.140@newsread1.news.pas.earthlink.net...
> >I am trying to check every field in the inserted and deleted tables in an
> > update trigger. I want to compare each field value between tables
because
> > I
> > don't want to log fields that haven't acutally changed. The only thing
> > that
> > comes to mind is to create a cursor based on the inserted/deleted tables
> > and
> > compare row by row, field by field. This seems way too cumbersome. Is
> > there a better way?
> >
> > Thanks.
> >
> > Matthew Wells
> > MWells@FirstByte.net
> >
> >
>
>
- Next message: Rick: "Subtotal"
- Previous message: Murphy: "Re: 2 tier vs 3 tier"
- In reply to: Itzik Ben-Gan: "Re: Iterate through the inserted table in an update trigger..."
- Next in thread: Steve Kass: "Re: Iterate through the inserted table in an update trigger..."
- Reply: Steve Kass: "Re: Iterate through the inserted table in an update trigger..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
