لطفا منتظر باشید ...
Triggers and Auditing
I've already touched on this a bit in a few of the examples, but this is a popular enough use of triggers that it bears further discussion. AFTER triggers are frequently used to create an audit trail of modifications to a table. You can simply record the modification act itself, or you can store the actual changes that were made. Here's a simple example of auditing implemented through triggers (Listing 8-10):
Listing 8-10 A simple auditing trigger.
USE tempdb
GO
CREATE TABLE ToyInventory
(Toy int identity,
Type int,
Onhand int
)
CREATE TABLE ToyAudit
(ToyAudit int identity,
Operation varchar(10),
Toy int,
Type int,
Change int
)
GO
CREATE TRIGGER ToyInventory_INSERT ON ToyInventory AFTER INSERT
AS
IF @@ROWCOUNT=0 RETURN
INSERT ToyAudit
SELECT 'INSERT', * FROM inserted
GO
INSERT ToyInventory DEFAULT VALUES
GO
Here we simply record any inserts into the ToyInventory table in a second table named ToyAudit. ToyAudit includes a column that indicates the operation that produced the log record. The insert trigger on ToyInventory supplies the string 'INSERT' for it.What if we wanted to do something a little more sophisticated? For example, what if we wanted to track the before and after image when a row is updated? Using an AFTER UPDATE trigger, this is pretty easy. Listing 8-11 shows how:
Listing 8-11 An audit trigger can capture the before and after images of a row.
CREATE TABLE ToyInventory
(Toy int identity,
Type int,
Onhand int
)
GO
CREATE TABLE ToyAudit
(ToyAudit int identity,
Operation varchar(20),
Toy int,
Type int,
Onhand int
)
GO
INSERT ToyInventory (Type, Onhand) VALUES (1, 50)
INSERT ToyInventory (Type, Onhand) VALUES (2, 50)
INSERT ToyInventory (Type, Onhand) VALUES (3, 50)
INSERT ToyInventory (Type, Onhand) VALUES (4, 50)
GO
CREATE TRIGGER ToyInventory_UPDATE ON ToyInventory AFTER UPDATE
AS
IF @@ROWCOUNT=0 RETURN
INSERT ToyAudit (Operation, Toy, Type, Onhand)
SELECT 'UPDATE--BEFORE', * FROM deleted ORDER BY Toy
INSERT ToyAudit (Operation, Toy, Type, Onhand)
SELECT 'UPDATE-AFTER', * FROM inserted ORDER BY Toy
GO
UPDATE ToyInventory SET Onhand = 49
GO
SELECT * FROM ToyAudit ORDER BY Toy, Operation, ToyAudit
(Results)
ToyAudit Operation Toy Type Onhand
----------- -------------------- ----------- ----------- -----------
1 UPDATE--BEFORE 1 1 50
5 UPDATE-AFTER 1 1 49
2 UPDATE--BEFORE 2 2 50
6 UPDATE-AFTER 2 2 49
3 UPDATE--BEFORE 3 3 50
7 UPDATE-AFTER 3 3 49
4 UPDATE--BEFORE 4 4 50
8 UPDATE-AFTER 4 4 49
This code demonstrates several techniques worth discussing further. First, it uses the logical table containing the before image of the updated rowsthe deleted tableto insert them into the audit table. It then does the same for the after-image table, inserted, and inserts the after-image rows into the audit table. When it selects from each of the logical tables, it orders the insertion using the Toy column so that the identity values in the ToyAudit table will be generated in Toy sequence. This allows us to later select the rows in the ToyAudit table using Toy as the high-order key and ToyAudit as the low-order key. This produces the result set you see in the listing where a row's after image immediately follows its before image, even though they originally came from two different logical tables.Note the use of a double hyphen to force the before-image audit records to sort before the after ones. Because we're sorting alphabetically on the Operation column, we need a way of sorting the word BEFORE ahead of AFTER. Inserting the second hyphen is a cheap and easy way to do this.What if we wanted to audit which columns were changed? What if we wanted to include the list of modified columns when we added log records to the audit table? This is pretty easy to do using our earlier technique of cross-referencing the bitmap returned by COLUMNS_UPDATED() with the ORDINAL_POSITION column in INFORMATION_SCHEMA.COLUMNS. Here's the code (Listing 8-12):
Listing 8-12 Using COLUMNS_UPDATED(), an audit trigger can record the names of modified columns.
USE tempdb
GO
CREATE TABLE ToyInventory
(Toy int identity,
Type int,
Onhand int
)
GO
CREATE TABLE ToyAudit
(ToyAudit int identity,
Operation varchar(20),
Toy int,
Type int,
Onhand int,
ColumnsModified varchar(7000)
)
GO
INSERT ToyInventory (Type, Onhand) VALUES (1, 50)
INSERT ToyInventory (Type, Onhand) VALUES (2, 50)
INSERT ToyInventory (Type, Onhand) VALUES (3, 50)
INSERT ToyInventory (Type, Onhand) VALUES (4, 50)
GO
CREATE TRIGGER ToyInventory_UPDATE ON ToyInventory AFTER UPDATE
AS
IF @@ROWCOUNT=0 RETURN
DECLARE @ChangedColumns varbinary(8000)
SET @ChangedColumns=COLUMNS_UPDATED()
INSERT ToyAudit (Operation, Toy, Type, Onhand, ColumnsModified)
SELECT 'UPDATE--BEFORE', d.*, c.COLUMN_NAME
FROM deleted d JOIN INFORMATION_SCHEMA.COLUMNS c
ON ((c.TABLE_NAME='ToyInventory') AND ((@ChangedColumns &
POWER(2,c.ORDINAL_POSITION-1))<>0))
ORDER BY d.Toy
INSERT ToyAudit (Operation, Toy, Type, Onhand, ColumnsModified)
SELECT 'UPDATE-AFTER', i.*, c.COLUMN_NAME
FROM inserted i JOIN INFORMATION_SCHEMA.COLUMNS c
ON ((c.TABLE_NAME='ToyInventory') AND ((@ChangedColumns &
POWER(2,c.ORDINAL_POSITION-1))<>0))
ORDER BY i.Toy
GO
UPDATE ToyInventory SET Onhand = 49, Type=3
GO
SELECT * FROM ToyAudit ORDER BY Toy, Operation, ToyAudit, ColumnsModified
(Results)
ToyAudit Operation Toy Type Onhand ColumnsModified
-------- --------------- ----- ----- ------ ---------------
1 UPDATE--BEFORE 1 1 50 Type
2 UPDATE--BEFORE 1 1 50 Onhand
9 UPDATE-AFTER 1 3 49 Type
10 UPDATE-AFTER 1 3 49 Onhand
3 UPDATE--BEFORE 2 2 50 Onhand
4 UPDATE--BEFORE 2 2 50 Type
11 UPDATE-AFTER 2 3 49 Onhand
12 UPDATE-AFTER 2 3 49 Type
5 UPDATE--BEFORE 3 3 50 Type
6 UPDATE--BEFORE 3 3 50 Onhand
13 UPDATE-AFTER 3 3 49 Type
14 UPDATE-AFTER 3 3 49 Onhand
7 UPDATE--BEFORE 4 4 50 Onhand
8 UPDATE--BEFORE 4 4 50 Type
15 UPDATE-AFTER 4 3 49 Onhand
16 UPDATE-AFTER 4 3 49 Type
