TF3p8AAb

1show databases;
2create database terramoto;
3
4use terramoto;
5
6
7CREATE TABLE Employees
8    (
9      EmployeeID integer NOT NULL AUTO_INCREMENT,
10      EmployeeName VARCHAR(50) ,
11      EmployeeAddress VARCHAR(50) ,
12      MonthSalary float,
13      PRIMARY KEY (EmployeeID)
14    );
15    
16CREATE TABLE EmployeesAudit
17    (
18      AuditID INTEGER NOT NULL,
19      EmployeeID INTEGER ,
20      EmployeeName VARCHAR(50) ,
21      EmployeeAddress VARCHAR(50) ,
22      MonthSalary float(10, 2) ,
23      ModifiedBy VARCHAR(128) ,
24      ModifiedDate DATETIME ,
25      Operation varchar(50) ,
26      PRIMARY KEY ( AuditID )
27    );
28    
29INSERT INTO Employees
30        ( EmployeeName ,
31          EmployeeAddress ,
32          MonthSalary
33        )
34SELECT 'Mark Smith', 'Ocean Dr 1234', 10000
35UNION ALL
36SELECT 'Joe Wright', 'Evergreen 1234', 10000
37UNION ALL
38SELECT 'John Doe', 'International Dr 1234', 10000
39UNION ALL
40SELECT 'Peter Rodriguez', '74 Street 1234', 10000
41
42CREATE TRIGGER TR_Audit_Employees ON Employees
43    FOR INSERT, UPDATE, DELETE
44AS
45    DECLARE @login_name VARCHAR(128)
46 
47    SELECT  @login_name = login_name
48    FROM    sys.dm_exec_sessions
49    WHERE   session_id = @@SPID
50 
51    IF EXISTS ( SELECT 0 FROM Deleted )
52        BEGIN
53            IF EXISTS ( SELECT 0 FROM Inserted )
54                BEGIN
55                    INSERT  INTO dbo.EmployeesAudit
56                            ( EmployeeID ,
57                              EmployeeName ,
58                              EmployeeAddress ,
59                              MonthSalary ,
60                              ModifiedBy ,
61                              ModifiedDate ,
62                              Operation
63                            )
64                            SELECT  D.EmployeeID ,
65                                    D.EmployeeName ,
66                                    D.EmployeeAddress ,
67                                    D.MonthSalary ,
68                                    @login_name ,
69                                    GETDATE() ,
70                                    'U'
71                            FROM    Deleted D
72                END
73            ELSE
74                BEGIN
75                    INSERT  INTO dbo.EmployeesAudit
76                            ( EmployeeID ,
77                              EmployeeName ,
78                              EmployeeAddress ,
79                              MonthSalary ,
80                              ModifiedBy ,
81                              ModifiedDate ,
82                              Operation
83                            )
84                            SELECT  D.EmployeeID ,
85                                    D.EmployeeName ,
86                                    D.EmployeeAddress ,
87                                    D.MonthSalary ,
88                                    @login_name ,
89                                    GETDATE() ,
90                                    'D'
91                            FROM    Deleted D
92                END  
93        END
94    ELSE
95        BEGIN
96            INSERT  INTO dbo.EmployeesAudit
97                    ( EmployeeID ,
98                      EmployeeName ,
99                      EmployeeAddress ,
100                      MonthSalary ,
101                      ModifiedBy ,
102                      ModifiedDate ,
103                      Operation
104                    )
105                    SELECT  I.EmployeeID ,
106                            I.EmployeeName ,
107                            I.EmployeeAddress ,
108                            I.MonthSalary ,
109                            @login_name ,
110                            GETDATE() ,
111                            'I'
112                    FROM    Inserted I
113        END