Trigger Help

[Jeff via AccessMonster.com]

I have an audit table that gets inserted with records on the update trigger
of the table it audits. My problem is that I only to update the fields that
have been changed in the audit table (i.e. any fields not changed should be
null). Here is my trigger:

CREATE TRIGGER UpdatePeople ON dbo.People
FOR UPDATE
AS
INSERT [People_Audit]
(SYSID, Name, Address, ChangeType, ChangeDate, ChangedBy)
SELECT SYSID, Name, Address, 'Update', GetDate(), SUSER_SNAME()
FROM inserted

My guess is that in the SELECT statement I should have something like IF
Update(...) for each field but I'm not too sure on this and can't get it to
work. Thanks,

 

[Sylvain Lafontaine]

Hi, I wanted to make a testbed before replying but I didn't get the time, so
I reply now without having tested the following; so pardon me if there are
any syntax error. The first solution would be to use local variables to
hold the values of modified columns or null otherwise:

DECLARE @name as varchar (50)

If Update ([name])
set @name = [name]
else
set @name = null

After that, all you have to do is to make your insert using your local
variables. You may also have to take into account the fact that you may
have more than one row inserted:

declare @numrows int
set @numrows = @@rowcount

if (@numrows > 1)
begin
raiserror (' ... blablabla ... ', 10, 1)
rollback transaction
end

Finally, a second solution would be to use the Case ... Else .. End
instruction if you want to
directly use the IF UPDATE (column) in your Insert statement; something
like:

.... Case When If Update (column) then Column Else Null End ....

However, I didn't make the test to see if this second solution could be used
for inserts with multiple rows (when @@rowcount > 1).

Finally, you should ask your question to m.p.sqlserver.programming; where
you will find a lot of people with a deep knowledge of SQL-Server. This
newsgroup is more about the client side of ADP, not its server side. You
can also reply or repost here if you want a complete example fully tested.

Hope that help.

 

[Vadim Rapp]

Hello Jeff,
You wrote in conference microsoft.public.access.adp.sqlserver on Wed, 08
Jun 2005 15:49:55 GMT:

JvA> I have an audit table that gets inserted with records on the update
JvA> trigger of the table it audits. My problem is that I only to update
JvA> the fields that have been changed in the audit table (i.e. any fields
JvA> not changed should be null). Here is my trigger:

JvA> CREATE TRIGGER UpdatePeople ON dbo.People
JvA> FOR UPDATE
JvA> AS
JvA> INSERT [People_Audit]
JvA> (SYSID, Name, Address, ChangeType, ChangeDate, ChangedBy)
JvA> SELECT SYSID, Name, Address, 'Update', GetDate(), SUSER_SNAME()
JvA> FROM inserted

Assuming SYSID if the primary key, you don't update it, but store in the
audit table,

insert into people_audit
(SYSID, Name, ChangeType, ...)
select
inserted.identitycol,
nullif(inserted.name,deleted.name),
nullif(inserted.ChangeType,deleted.ChangeType),
....
from inserted inner join deleted on inserted.identitycol=deleted.identitycol

Vadim