Ask a new Question

Question

1.CREATE the following tables -

a.Dept_triggers - Add Identity Column (1000,1)

b.Emp_triggers- Add Identity Column (1000,1)

c.Emphistory

2.TRIGGERS

a.Trigger 1 - Build a trigger on the emp table after insert that adds a record into the emp_History table and marks IsActive column to 1

b.Trigger 2 - Build a tirgger on the emp table after an update of the empname or deptid column - It updates the subsequent empname and/or deptid in the emp_history table.

c.Build a trigger on the emp table after delete that marks the isactive status = 0 in the emp_History table.

3.Run this script - Results should show 10 records in the emp history table all with an active status of 0

INSERT INTO dbo.emp_triggers

SELECT 'Ali',1000

INSERT INTO dbo.emp_triggers

SELECT 'Buba',1000

INSERT INTO dbo.emp_triggers

SELECT 'Cat',1001

INSERT INTO dbo.emp_triggers

SELECT 'Doggy',1001

INSERT INTO dbo.emp_triggers

SELECT 'Elephant',1002

INSERT INTO dbo.emp_triggers

SELECT 'Fish',1002

INSERT INTO dbo.emp_triggers

SELECT 'George',1003

INSERT INTO dbo.emp_triggers

SELECT 'Mike',1003

INSERT INTO dbo.emp_triggers

SELECT 'Anand',1004

INSERT INTO dbo.emp_triggers

SELECT 'Kishan',1004

DELETE FROM dbo.emp_triggers

4.Create 5 views - Each view will use 3 tables and have 9 columns with 3 coming from each table.

a.Create view using 3 Human Resources Tables (Utilize the WHERE clause)

b.Create view using 3 Person Tables (Utilize 3 system functions)

c.Create view using 3 Production Tables (Utilize the Group By Statement)

d.Create view using 3 Purchasing Tables (Utilize the HAVING clause)

e.Create view using 3 Sales Tables (Utilize the CASE Statement)