March 15, 2007 at 1:16 am
HI,
This is regarding my Audit Trail Data Base. I've created an Instead of trigger for a History table. But I have to create this trigger for multiple tables. And fields in all those tables differ.
The trigger I created is given here
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
go
ALTER
TRIGGER [TR_tbl_Client_History] ON [dbo].[tbl_Client_History] INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@UserName varchar(100);
select
@UserName = loginame
from
master
.dbo.sysprocesses
where
spid
= @@SPID;
IF
(@UserName='xerimis')
BEGIN
INSERT
INTO [tbl_Client_History]
(
[ClientID] ,[ClientSerialNo] ,[ClientName] ,[ClientNo] ,[CreatedBy] ,[CreatedAt] ,[ModifiedBy] ,[ModifiedAt] ,[Action])
SELECT
[ClientID] ,[ClientSerialNo] ,[ClientName] ,[ClientNo] ,[CreatedBy] ,[CreatedAt] ,[ModifiedBy] ,[ModifiedAt] ,[Action]
FROM Inserted
END
ELSE
BEGIN
RAISERROR ('You are not Authorized to change the database.',16,1)
END
END
Can anybody help me on this??
Thanks
March 15, 2007 at 10:14 am
Have you use INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS before? Well you can create a script to select from the TABLE schemas the tables you need, then the columns. Build a couple of list and pass them to a SP where you will build the SQL command. This gets ugly some times, so when testing instead of run the exec sp_executesql run select @SQLCommand and copy/paste the query and test it. It is all worth if you have more than 10-20 tables, if not just "bite the dust" and copy paste you trigger script.
March 15, 2007 at 11:31 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply