-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: CSharpMadhu
Message 1 in Discussion
Hi All, I am writing a trigger which would copy the record that was
inserted/updated/deleted into an audit table. I can do this by simply writing a
Trigger each for Insert, Update and Delete for a particular table. But as the number
of Tables is more, I will end up writing a lot of triggers. Therefore I was looking
for a way to write a single trigger for a table which would work in all the modes
(insert/update/delete). The question is how do I know what made the trigger fire?
I've heard of COLUMN_UPDATE(), but don't know how I can use it here. I am pasting my
current code here. -- 1. TRIGGER FOR INSERT IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'TBL_CARD_PAYMENT_DTLS_INSERT' AND type = 'TR')
DROP TRIGGER TBL_CARD_PAYMENT_DTLS_INSERT
GO
CREATE TRIGGER TBL_CARD_PAYMENT_DTLS_INSERT
ON TBL_CARD_PAYMENT_DTLS
FOR INSERT
AS
DECLARE @UserName VARCHAR(50)
SELECT @UserName = U.USER_NAME FROM TBL_USERS U, INSERTED I
WHERE U.USER_ID = I.USER_ID
INSERT TBL_AUDIT_CARD_PAYMENT_DTLS
SELECT
*,
@UserName,
GetDate(),
'INSERT'
FROM INSERTED
-- 2. TRIGGER FOR UPDATE IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'TBL_CARD_PAYMENT_DTLS_UPDATE' AND type = 'TR')
DROP TRIGGER TBL_CARD_PAYMENT_DTLS_UPDATE
GO CREATE TRIGGER TBL_CARD_PAYMENT_DTLS_UPDATE ON TBL_CARD_PAYMENT_DTLS
AFTER UPDATE
AS
DECLARE @UserName VARCHAR(50)
SELECT @UserName = U.USER_NAME FROM TBL_USERS U, INSERTED I
WHERE U.USER_ID = I.USER_ID
INSERT TBL_AUDIT_CARD_PAYMENT_DTLS
SELECT
I.*,
@UserName,
GetDate(),
'UPDATE'
FROM INSERTED I -- 3. TRIGGER FOR DELETE IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'TBL_CARD_PAYMENT_DTLS_DELETE' AND type = 'TR')
DROP TRIGGER TBL_CARD_PAYMENT_DTLS_DELETE
GO CREATE TRIGGER TBL_CARD_PAYMENT_DTLS_DELETE ON TBL_CARD_PAYMENT_DTLS
AFTER DELETE
AS
DECLARE @UserName VARCHAR(50)
SELECT @UserName = U.USER_NAME FROM TBL_USERS U, DELETED D
WHERE U.USER_ID = D.USER_ID
INSERT TBL_AUDIT_CARD_PAYMENT_DTLS
SELECT
*,
@UserName,
GetDate(),
'DELETE'
FROM DELETED
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/BDotNet/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you received
this message by mistake, please click the "Remove" link below. On the pre-addressed
e-mail message that opens, simply click "Send". Your e-mail address will be deleted
from this group's mailing list.
mailto:[EMAIL PROTECTED]
