April 11, 2005 at 12:54 pm
I want to have an Audit Table update any Inserts, Deletes or Updates.
This is what I have presently,
--INSERT INTO AUDITLOG new USERS
IF EXISTS (SELECT * from sysobjects
where name = 'trg_USER_I' and xtype = 'TR')
drop trigger trg_USER_I
go
CREATE TRIGGER trg_USER_I on USERS for Insert
as
if update ([name])
insert AUDITLOG
(
InsertDate
,Username
,initials
,facsimileTelephoneNumber
,streetAddress
,manager
,whenCreated
,uSNChanged
,DN
,objectClass
,[name]
,objectGUID
,[description]
,displayName
,userAccountControl
,sAMAccountName
,sn
,givenName
,memberOf
,homeMTA
,proxyAddresses
,homeMDB
,mDBUseDefaults
,mailNickname
,msExchHomeServerName
,physicalDeliveryOfficeName
,telephoneNumber
,department
,homeDirectory
,sIDHistory
,directReports
,homeDrive
,targetAddress
,c
,l
,st
,postalCode
,mAPIRecipient
,Logtype)
select
InsertDate
,Username
,initials
,facsimileTelephoneNumber
,streetAddress
,manager
,whenCreated
,uSNChanged
,DN
,objectClass
,[name]
,objectGUID
,[description]
,displayName
,userAccountControl
,sAMAccountName
,sn
,givenName
,memberOf
,homeMTA
,proxyAddresses
,homeMDB
,mDBUseDefaults
,mailNickname
,msExchHomeServerName
,physicalDeliveryOfficeName
,telephoneNumber
,department
,homeDirectory
,sIDHistory
,directReports
,homeDrive
,targetAddress
,c
,l
,st
,postalCode
,mAPIRecipient
,'I'
from inserted I
and it works. This example only updates if [name] is updated. I know I can write different triggers; one for each field; however is there an easier way I can use it.
"If anything the following fields are update then insert"
Thanks for you help
April 11, 2005 at 12:57 pm
if update ([name]) or update(col2) or update (coln)
...
The rest seems fine. One thing that can save a lot of cpu cycles is to check that any actual data has been modified, but with that many columns I don't think you'd save enough time to justify a join + 40 where conditions.
April 11, 2005 at 12:59 pm
Thanks. So simple when you point it out to me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply