Step 1 : Create table audits via triggers The system must log any insertion, del

Question

Step 1: Create table audits via triggers

The system must log any insertion, deletion, or updates to the following tables:

•Employee table (project 1)

•Job table (project 1)

•ProjectMain table (Project 2)

•ActivityMain table (Project 2)

For each one of the table above, you will write the SQL Script to create their respective AUDIT table which is a table that contains the same columns as the original table, plus the additional columns Operation and DateTimeStamp

For example, for the EMPLOYEE table with the given columns, you will create EMPLOYEEAUDIT:

Employee                                ->                        EmployeeAudit

•empNumber (char(8)),                                         -empNumber (char(8)),

firstName (varchar(25)),            -                           firstName (varchar(25)),

•lastName varchar(25)),                                        -lastName varchar(25)),

•ssn (char(9)),                                                        -ssn (char(9)),

•address (varchar(50)),                                          -address (varchar(50)),

•state (char(2)),                                                     -state (char(2)),

•zip (char(5))                                                          -zip (char(5)),

•jobCode (char(4)),                                                -jobCode (char(4)) ,

•dateOfBirth (date),                                               -dateOfBirth (date),

•certification(bit),                                                   -certification(bit),

•salary(money))                                                    -salary(money) )

•                                                                            -Operation (varchar(50))

•                                                                            -DateTimeStamp (datetime)

Therefore, your assignment script will create the following 4 Audit tables:

-EmployeeAudit

-JobAudit

-ProjectMainAudit

-ActivityMainAudit

NOTE: You MUST use the above names for the audit tab

You will create the following 4 triggers:

-trgEmployee: Will be placed on the Employee table and listens for Inserts, Deletes, and Updates

-trgJob: Will be placed on the Job table and listens for Inserts, Deletes,

and Updates

-trgProjectMain:Will be placed on the ProjectMain table that contains the projectId and projectName and listens for Inserts, Deletes, and Updates

-trgActivityMain: Will be placed on the ActivityMain table that contains the activityId and activityName and listens for Inserts, Deletes, and Updates.

Again, each trigger will write to its respective audit table:

trgProjectMain will write to ProjectMainAudit

trgActivityMain will write to ActivityMainAudit

trgEmployee will write to EmployeeAudit

trgJob will write to JobAudit

Again, the columns which will be written to the audit tables will be all the original columns plus “Operation” and “DateTimeStamp”

The trigger will support the Insert, Delete and Update operation as follows:

•If a record is inserted in the original table, then the audit table will contain the original values plus “INSERTED” into the operation column with the datetime stamp.

•For the Delete operation, it will contain “DELETED” in the operation column

plus the datetimestamp.

•For the Update operation, it will contain 2 records in the audit table, one for the

old values (Operation will have

DELETED) and one for the new values (Operation will have INSERTED).

Step 2: System Catalog Scripts

Using the System Catalog Views

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/object-catalog-views-transact-sql

create the SQL Scripts for the following views:

1.vw_TableNoIndexes: User tables with no Indexes

2.vw_ProjectIdTables: All the tables which contain the column “projectId”

3.vw_Last7Obj: All the objects that have been modified in the last 7 days

4.vw_ProjectProcs: The SQL logic from the stored procedures which have “Project” in their name

Step 3: DBA Troubleshooting

Create the following Stored Procedures to assist in performing a database administrator tasks.

Sp_ActiveConnections

Return all the active connections for a given database

name.

Parameters: @databasename varchar(250)

Hint: Use sys.sysprocesses. You should do some grouping to a count of the connections.

OutPut:

DatabaseName        NumberOfConnections           LoginName

COP4703                             1                                   cjanv002

Sp_LogFileStatus

Return all the status of all the transaction log files for a given database name.

Parameters: @databasename varchar(250)

Hint: Use sys.master_files from the Database Health Monitoring document.

OutPut:

DatabaseName               LogSize                                 TotalSize

COP4703                            8                                               16

Explanation / Answer

CREATE TABLE EmployeeAudit

(

empNumber char(8),

firstName varchar(25)

lastName varchar(25),

ssn char(9),

address varchar(50),

state char(2),

zip char(5),

jobCode char(4),

dateOfBirth (date),

certification(bit),

salary(money),

Operation varchar(50),

DateTimeStamp (datetime)

)

CREATE TRIGGER trgEmployee ON Employee

FOR INSERT

AS

declare @empNumber char(8);

declare @firstName varchar(25);

declare @lastName varchar(25);

declare @ssn char(9);

declare @address varchar(50);

declare @state char(2);

declare @zip char(5);

declare @jobCode char(4);

declare @dateOfBirth (date);

declare @certification(bit);

declare @salary(money);

declare @Operation varchar(50);

declare @DateTimeStamp (datetime);

select @empNumber=i.empNumber from inserterd i;

select @firstName=i.firstName from inserterd i;

select @lastName=i.lastName from inserterd i;

select @ssn=i.ssn from inserterd i;

select @address=i.address from inserterd i;

select @state=i.state from inserterd i;

select @zip=i.zip from inserterd i;

select @jobCode=i.jobCode from inserterd i;

select @dateOfBirth=i.dateOfBirth from inserterd i;

select @certification=i.certification from inserterd i;

select @salary=i.salary from inserterd i;

select @Operation=' Inserted Record -- After Trigger.';

     insert into EmployeeAudit           (empNumber,firstName,lastName,ssn,address,state,zip,jobCode,dateOfBirth,certification,salary,Operation,DateTimeStamp)

values(@empNumber,@firstName,@lastName,@ssn,@address,@state,@zip,@jobCode,@dateOfBirth,@certification,@salary,@Operation,getdate());

        PRINT ' Insert Trigger fired.'

GO

CREATE TRIGGER trgEmployee ON Employee

FOR UPDATE

AS

declare @empNumber char(8);

declare @firstName varchar(25);

declare @lastName varchar(25);

declare @ssn char(9);

declare @address varchar(50);

declare @state char(2);

declare @zip char(5);

declare @jobCode char(4);

declare @dateOfBirth (date);

declare @certification(bit);

declare @salary(money);

declare @Operation varchar(50);

declare @DateTimeStamp (datetime);

select @empNumber=i.empNumber from inserterd i;

select @firstName=i.firstName from inserterd i;

select @lastName=i.lastName from inserterd i;

select @ssn=i.ssn from inserterd i;

select @address=i.address from inserterd i;

select @state=i.state from inserterd i;

select @zip=i.zip from inserterd i;

select @jobCode=i.jobCode from inserterd i;

select @dateOfBirth=i.dateOfBirth from inserterd i;

select @certification=i.certification from inserterd i;

select @salary=i.salary from inserterd i;

    insert into EmployeeAudit           (empNumber,firstName,lastName,ssn,address,state,zip,jobCode,dateOfBirth,certification,salary,Operation,DateTimeStamp)

values(@empNumber,@firstName,@lastName,@ssn,@address,@state,@zip,@jobCode,@dateOfBirth,@certification,@salary,@Operation,getdate());

        PRINT ' Update Trigger fired.'

GO

CREATE TRIGGER trgEmployee ON Employee

FOR DELETE

AS

declare @empNumber char(8);

declare @firstName varchar(25);

declare @lastName varchar(25);

declare @ssn char(9);

declare @address varchar(50);

declare @state char(2);

declare @zip char(5);

declare @jobCode char(4);

declare @dateOfBirth (date);

declare @certification(bit);

declare @salary(money);

declare @Operation varchar(50);

declare @DateTimeStamp (datetime);

select @empNumber=i.empNumber from deleted i;

select @firstName=i.firstName from deleted i;

select @lastName=i.lastName from deleted i;

select @ssn=i.ssn from deleted i;

select @address=i.address from deleted i;

select @state=i.state from deleted i;

select @zip=i.zip from deletedi;

select @jobCode=i.jobCode from deleted i;

select @dateOfBirth=i.dateOfBirth from deleted i;

select @certification=i.certification from deleted i;

select @salary=i.salary from deleted i;

select @Operation='Record -- After Delete Trigger.';

     insert into EmployeeAudit           (empNumber,firstName,lastName,ssn,address,state,zip,jobCode,dateOfBirth,certification,salary,Operation,DateTimeStamp)

values(@empNumber,@firstName,@lastName,@ssn,@address,@state,@zip,@jobCode,@dateOfBirth,@certification,@salary,@Operation,getdate());

        PRINT ' Delete Trigger fired.'

GO

Sp_ActiveConnections

Sp_LogFileStatus

ALTER DATABASE databasename
ADD LOG FILE
(
NAME = sys.master_files,
FILENAME = 'sys.master_files',
SIZE = 8MB,
MAXSIZE = 18MB,
FILEGROWTH = 5MB
);

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.