[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Create Trigger

Manny Chohan

4/1/2007 5:20:00 PM

Hello,

I have Accounts and Session_History table with following schema:
Accounts AccountID int PK
AccountName varchar
CreateUser varchar

Session_History RecordType char(3) "ACC"
RecordID int
"ACCOUNTID"
UserID varchar
I have multiple tables in my database i.e contacts, accouts, leads etc. I
would like to maintain history of what the user does therefore need a
update,delete and insert trigger to enter data into the session_history
table.

Can someone please post how to create a trigger to enter record in
session_History table whenever a record is created, updated or deleted in the
Accounts Table?

Thanks

Manny

2 Answers

TheSQLGuru

4/1/2007 6:03:00 PM

0

Typically for an auditing situation, you insert the appropriate records in
tables with an identical structure as the one being audited (with perhaps a
field or two added for things like date/userid).

Trying to put data from a variety of tables into a single audit table would
entail something like casting every field to a varchar and concatenating
them, or perhaps mashing them into XML and putting them into an XML column.
Not very straight forward activity, and the data may be less than useful
also.

You may wish to take a look at the Audit product from ApexSQL. This will
build out an auditing system for you, with a number of nifty features.

--
TheSQLGuru
President
Indicium Resources, Inc.

"Manny Chohan" <MannyChohan@discussions.microsoft.com> wrote in message
news:FB69DB4B-CD4D-4484-B851-9B066F7DC1C4@microsoft.com...
> Hello,
>
> I have Accounts and Session_History table with following schema:
> Accounts AccountID int PK
> AccountName varchar
> CreateUser varchar
>
> Session_History RecordType char(3) "ACC"
> RecordID int
> "ACCOUNTID"
> UserID varchar
> I have multiple tables in my database i.e contacts, accouts, leads etc. I
> would like to maintain history of what the user does therefore need a
> update,delete and insert trigger to enter data into the session_history
> table.
>
> Can someone please post how to create a trigger to enter record in
> session_History table whenever a record is created, updated or deleted in
> the
> Accounts Table?
>
> Thanks
>
> Manny
>


Erland Sommarskog

4/1/2007 8:00:00 PM

0

Manny Chohan (MannyChohan@discussions.microsoft.com) writes:
> I have Accounts and Session_History table with following schema:
> Accounts AccountID int PK
> AccountName varchar
> CreateUser varchar
>
> Session_History RecordType char(3) "ACC"
> RecordID int
> "ACCOUNTID"
> UserID varchar
> I have multiple tables in my database i.e contacts, accouts, leads etc. I
> would like to maintain history of what the user does therefore need a
> update,delete and insert trigger to enter data into the session_history
> table.
>
> Can someone please post how to create a trigger to enter record in
> session_History table whenever a record is created, updated or deleted
> in the Accounts Table?

It's not entirely clear how the data in Accounts maps to Sessiion_History
table. Even less you would distinguish between different types of
operations. For what you have posted, the best I can make out is:

CREATE TABLE history_ins_upd_tri ON Accounts FOR INSERT, UPDATE AS
INSERT Session_History (RecordTYpe, RecordID, UserID)
SELECT 'ACC', AccountID, SYSTEM_USER
go
CREATE TABLE history_del_tri ON Accounts FOR DELETE AS
INSERT Session_History (RecordTYpe, RecordID, UserID)
SELECT 'ACC', AccountID, SYSTEM_USER
FROM deleted

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/...