Notes

SELECT * FROM Employee;
Employee_Audit_Test' to automatically store transaction records of each operation,
such as INSERT, UPDATE, or DELETE on the Employee table:
1. CREATE TABLE Employee_Audit_Test
2. (
3. Id int IDENTITY,
4. Audit_Action text
5. )
Now, we will create a trigger that stores transaction records of each insert
operation on the Employee table into the Employee_Audit_Test table. Here we are
going to create the insert trigger using the below statement:
1. CREATE TRIGGER trInsertEmployee
2. ON Employee
3. FOR INSERT
4. AS
5. BEGIN
Declare @Id int
7. SELECT @Id = Id from inserted
8. INSERT INTO Employee_Audit_Test
9. VALUES ('New employee with Id = ' + CAST(@Id AS VARCHAR(10)) + ' is ad
ded at ' + CAST(Getdate() AS VARCHAR(22)))
10. END
After creating a trigger, we will try to add the following record into the table:
1. INSERT INTO Employee VALUES (6,'Peter', 62000, 'Male', 3)
If no error is found, execute the SELECT statement to check the audit records. We will
get the output as follows:
We are going to create another trigger to store transaction records of each delete
operation on the Employee table into the Employee_Audit_Test table. We can create
the delete trigger using the below statement:
1. CREATE TRIGGER trDeleteEmployee
2. ON Employee
3. FOR DELETE
4. AS
5. BEGIN
6. Declare @Id int
7. SELECT @Id = Id from deleted
8. INSERT INTO Employee_Audit_Test
9. VALUES ('An existing employee with Id = ' + CAST(@Id AS VARCHAR(10)) +
' is deleted at ' + CAST(Getdate() AS VARCHAR(22)))
10. END
After creating a trigger, we will delete a record from the Employee table:
1. DELETE FROM Employee WHERE Id = 2;
1. CREATE TRIGGER schema_name.trigger_name
2. ON table_name
3. AFTER {INSERT | UPDATE | DELETE}
4. AS
5. BEGIN
6. -- Trigger Statements
7. -- Insert, Update, Or Delete Statements
8. END
DROP TRIGGER schema_name.trigger_name1;