Hello could you help me code this for mySql Step 1: Create table audits via trig

Question

Hello could you help me code this for mySql

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 tables.

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                                        aroque

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

Creating Trigger Trigger_1:

CREATE TRIGGER trgProject_Insert ON ProjectAudit

FOR INSERT

AS

declare @projectID char(4),

declare @projectName VARCHAR (50),

declare @fundedBudget DECIMAL(16,2),

declare @firmFedID CHAR(9),

declare @startDate DATE,

declare @projectTypeCode CHAR(5),

declare @projectTypeDesc VARCHAR (50),

declare @projectStatus VARCHAR(25),

declare @projectEndDate DATE,

declare @projectManager CHAR(8)

insert into ProjectAudit values

(@projectID,@projectName,@fundedBudget,@firmFedID,@st

artDate,@projectTypeCode,@projectTypeDesc,@projectSta

tus,@projectEndDate,@projectManager);

PRINT 'AFTER INSERT trigger fired.'

CREATE TRIGGER Trg_ProjectAudit_Delete ON ProjectAudit

FOR DELETE

declare @projectID char(4)

select @projectID =d.projectManager from deleted d;

Delete from EmployeeAudit where empNumber=@projectID

Delete from ProjectAudit where projectManager=@projectID

PRINT 'Delete trigger fired.'

CREATE TRIGGER Trg_ProjectAudit_Update

ON ProjectAudit

AFTER UPDATE

AS

declare @projectID char(4)

SELECT @projectID = INSERTED.projectID

FROM INSERTED

Update ProjectAudit Set projectName='Johns&Co' where

projectID=@projectID

Creating Trigger 2:

CREATE TRIGGER trgActivity_Insert ON ActivityAudit

FOR INSERT

AS

declare @activityID CHAR (4),

declare @activityName VARCHAR (50),

declare @activityTypeCode char (2),

declare @activityStatus VARCHAR (25),

declare @activityTypeDesc VARCHAR (50),

declare @costToDate DECIMAL (16,2),

declare @startDate DATE,

declare @endDate DATE,

declare @projectID char(4)

Insert into ActivityAudit values

(@activityID,@activityName,@activityTypeCode,@activit

yStatus,@activityTypeDesc,@costToDate,@startDate,@end

Date,@projectID)

CREATE TRIGGER trgActivity_Delete ON ActivityAudit

FOR DELETE

declare @projectID char(4)

select @projectID =d.projectID from deleted d;

Delete from EmployeeAudit where empNumber=@projectID

Delete from ProjectAudit where projectManager=@projectID

Delete from ActivityAudit where projectID=@projectID

CREATE TRIGGER trgActivity_Update

ON ActivityAudit

AFTER Update

AS

declare @activityID char(4)

SELECT @activityID = INSERTED.activityID

FROM INSERTED

Update ActivityAudit Set activityName='Insert a record' where

activityID=@activityID

Creating Trigger 3:

CREATE TRIGGER trgEmployee_Insert ON EmployeeAudit

FOR INSERT

AS

declare @empNumber CHAR (8),

declare @SSN CHAR (9),

declare @firstName VARCHAR (25),

declare @lastName VARCHAR (25),

declare @address VARCHAR (50),

declare @state CHAR (2),

declare @zip CHAR (5),

declare @job VARCHAR (50)

Insert into EmployeeAudit values(@empNumber,@SSN,@firstName,@lastName,@address,@state,@zip,@job)

CREATE TRIGGER trgEmployee_Delete ON EmployeeAudit

FOR DELETE
As
declare @empNumber CHAR (8)

select @empNumber =d.empNumber from deleted d;

Delete from EmployeeAudit where empNumber=@empNumber

CREATE TRIGGER trgEmployee_Update ON EmployeeAudit

AFTER Update

declare @empNumber CHAR (8)

SELECT @empNumber= INSERTED.empNumber

FROM INSERTED

Update EmployeeAudit Set firstName='Jhon' where empNumber=@empNumber

Creating Trigger 4:

CREATE TRIGGER trgJob_Insert ON JobAudit

FOR INSERT

AS

declare @jobCode(char(4),
declare @jobdesc(varchar(50)),
declare @Operation(varchar(50)),
declare @DateTimeStamp (datetime)

Insert into JobAudit values(@jobCode,@jobdesc,@Operation,@DateTimeStamp)

CREATE TRIGGER trgJob_Delete ON JobAudit

FOR DELETE

As

declare @jobCode(char(4))

select @jobCode =d.jobCode from deleted d;

Delete from JobAudit where jobCode=@jobCode

CREATE TRIGGER trgJob_Update ON EmployeeAudit

AFTER Update

declare @jobCode CHAR (4)

SELECT @jobCode= INSERTED.jobCode

FROM INSERTED

Update JobAudit Set Operation='Create' where jobCode=@jobCode

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

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