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 -
trgProject: Will be placed on the Project table that contains the projectId and projectName.
- trgActivity: Will be placed on the Activity table that contains the activityId and activityName.
Again, each trigger will write to its respective audit table:
trgProject will write to ProjectAudit
trgActivity will write to ActivityAudit
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”
MY CODE SO FAR:
create table EmployeeAudit
(
empNumber char(8) PRIMARY KEY,
firstName varchar(25),
lastName varchar(25),
ssn char(9),
address varchar(50),
state char(2),
CONSTRAINT EMP_STATECHECK CHECK (state IN('CA', 'FL')),
zip char(5),
jobCode char(4)
CONSTRAINT FK_JOB FOREIGN KEY (jobCode)
REFERENCES Job(jobCode),
dateOfBirth date,
certification bit,
salary money,
Operation (varchar(50)),
DateTimeStamp (datetime)
);go
create table JobAudit
(
jobCode(char(4) PRIMARY KEY,
CONSTRAINT JOB_JOBCODE CHECK(jobCode IN( 'CAST', 'ENGI', 'INSP',
'PMGR')),
jobdesc(varchar(50)),
Operation (varchar(50)),
DateTimeStamp (datetime)
);go
create table ProjectAudit
(
projectId char(4),
primary key(projectId),
projectName varchar(50),
firmFedID char(9),
fundedbudget decimal(16,2),
projectStartDate date,
projectstatus varchar(25),
projectTypeCode char(5),
projectedEndDate date,
projectManager char(8),
activityID char(4),
Operation (varchar(50)),
DateTimeStamp (datetime)
);go
create table ActivityAudit
(
activityId char(4),
projectId char(4),
activityName varchar(50),
costToDate decimal(16,2),
activityStatus varchar(25),
startDate date,
endDate date
primary key(projectId,activityId),
Operation (varchar(50)),
DateTimeStamp (datetime)
);go
create trigger trgEmployee
();go
create trigger trgJob
();go
create trigger trgProject
();go
create trigger trgActivity
();go
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
You will create the following 4 triggers: - trgEmployee: Will be placed on the Employee table...
Step 1: Create table audits via triggers The system must log any insertion, deletion, or updates to the following tables: • Employee table (project 1) create table Employee ( empNumber char(8) not null, firstName varchar(25) null, lastName varchar(25) null, ssn char(9) null, address varchar(50) null, state char(2) null, zip char(5) null, jobCode char(4) null, dateOfBirth date null, certification bit null, salary money null, constraint PK_EMP PRIMARY KEY(empNumber), constraint EMP_STATECHECK CHECK(state in ('CA','FL')) ) GO • Job table (project 1) create...
- - Requirements Building upon your project 1 and project 2, the park will be a Star Wars themed park. You must design additional parts of the database and create the following SQL Script. 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...
Step 1: Design and create the tables You must create additional tables to hold Project and Activity Data. A project represents the construction of a facility with a limited scope of work and financial funding. A Project can be composed of many activities which indicate the different phases in the construction cycle. Example Project Name: Bobba Fett’s Bounty Chase Ride An activity represents the work that must be done to complete the project. Example Activity Name: For Example activity name...
MICROSOFT SQL SERVER - Create the following views 1.List the employee assigned to the most projects 2.List the project with the most hours SCRIPT TO BUILD DATABASE create table department ( dept_ID int not null , dept_name char(50) NOT NULL, manager_ID int not null, manager_start_date date not null, constraint Dept_PK primary key (dept_ID), constraint D_Name_AK unique (dept_name) ); insert into department values(1,'abc',1,'2019-01-08'); insert into department values(2,'abc2',2,'2019-01-08'); insert into department values(3,'abc3',2,'2019-01-08'); insert into department values(4,'abc4',2,'2019-01-08'); /*project table done*/ create table project...
Using the table below, Write 2 simple/short TRIGGER STATEMENTS (WITH GOOD BUSINESS VALUE), After writing the trigger statements, please comment on what the TRIGGER IS DOING. CREATE TABLE STUDENT_TBL ( STU_ID NUMBER (8) NULL, F_NAME VARCHAR (20) NULL, L_NAME VARCHAR (30) NULL, STU_MAJOR VARCHAR (20) NULL, STU_ADDR VARCHAR (50) NULL, STU_EMAIL VARCHAR (30) NULL, CONSTRAINT PKSTU primary key (STU_ID) ); DESCRIBE STUDENT_TBL; CREATE TABLE ENROLL_TBL ( ENR_ID NUMBER (8), ENR_DATE DATE (10)...
-- Schema definition create table Customer ( cid smallint not null, name varchar(20), city varchar(15), constraint customer_pk primary key (cid) ); create table Club ( club varchar(15) not null, desc varchar(50), constraint club_pk primary key (club) ); create table Member ( club varchar(15) not null, cid smallint not null, constraint member_pk primary key (club, cid), constraint mem_fk_club foreign key (club) references Club, constraint mem_fk_cust...
Database Management 6. [5] Create the following table: CREATE TABLE customer ( cust_name VARCHAR(30) NOT NULL, address VARCHAR(60), UNIQUE (cust name, address)); A. Run the following inserts and explain why both work and how will you prevent it INSERT INTO customer VALUES ('Alex Doe', NULL); INSERT INTO customer VALUES ('Alex Doe', NULL); 7. [5] Modify the following table definition to ensure that all employees have a minimum wage of $10 CREATE TABLE employee ( empId INT PRIMARY KEY, empName VARCHAR(40)...
Given the mySQL tables created below... Create a mySQL solution to return the itemIDs of items posted by user X, such that all the reviews are “Excellent” or “Good” for these items CREATE TABLE Users ( userId varchar (30) NOT NULL, pass varchar (30), fname varchar (50), lname varchar (50), email varchar (50), gender char(1), age integer, banned boolean, PRIMARY KEY (userId), UNIQUE(email)) CREATE TABLE FavSellers ( userId...
Write an SQL query to return the users who posted the most number of jokes on 1/1/2019. Database: create table User( userid varchar(30) not null, password varchar(30), firstname varchar(30), lastname varchar(50), email varchar(50), gender char(1), age integer, banned boolean, primary key(userid), unique(email)); create table MyFriends( userid varchar(30), friendid varchar(30), primary key(userid,friendid), foreign key(userid) references User, foreign key(friendid) references User(userid)); Create table Jokes( Jokeid integer, title varchar(100), description varchar(255), authorid varchar(30) not null, primary key(jokeid), posttime Datetime, foreign key(authorid) references User(userid));...
Based on reference below. Write a relational algebra expression to return those users who have posted “excellent” reviews but never “poor” reviews. CREATE TABLE Users ( userId varchar (30) NOT NULL, pass varchar (30), fname varchar (50), lname varchar (50), email varchar (50), gender char(1), age integer, banned boolean, PRIMARY KEY (userId), UNIQUE(email)) CREATE TABLE FavSellers ( userId varchar (30), sellerId varchar (30), PRIMARY KEY (userId, sellerId),...