Z76Nwc7F

1/*
2-- a)
3CREATE PROC Remove_Employee @SSN char(9) = null
4AS
5	IF @SSN is null
6	BEGIN
7		PRINT 'Please enter Employee Code!'
8		RETURN
9	END
10	DELETE FROM COMPANY.WORKS_ON
11	WHERE COMPANY.WORKS_ON.Essn = @SSN
12	DELETE FROM COMPANY.DEPENDENT
13	WHERE COMPANY.DEPENDENT.Essn = @SSN
14	UPDATE COMPANY.DEPARTMENT SET Mgr_ssn = NULL, Mgr_start_date = NULL WHERE Mgr_ssn = @SSN;
15	UPDATE COMPANY.EMPLOYEE SET Super_ssn = NULL WHERE Super_ssn = @SSN;
16	DELETE FROM COMPANY.EMPLOYEE
17	WHERE COMPANY.EMPLOYEE.Ssn = @SSN
18
19EXEC Remove_Employee '21312332';
20
21-- b) 
22 go
23CREATE PROCEDURE Get_Managers
24AS
25	SELECT COMPANY.EMPLOYEE.*
26	FROM COMPANY.DEPARTMENT JOIN COMPANY.EMPLOYEE ON COMPANY.DEPARTMENT.Mgr_ssn = COMPANY.EMPLOYEE.Ssn;
27	SELECT TOP 1 COMPANY.DEPARTMENT.Mgr_ssn, datediff(year,COMPANY.DEPARTMENT.Mgr_start_date,GETDATE()) AS anos
28	FROM COMPANY.DEPARTMENT
29	WHERE datediff(year,COMPANY.DEPARTMENT.Mgr_start_date,GETDATE()) is not NULL
30	ORDER BY anos desc
31	
32go
33
34EXEC Get_Managers;
35
36-- c)
37
38CREATE TRIGGER Already_Manager ON COMPANY.DEPARTMENT
39INSTEAD OF INSERT, UPDATE
40AS
41BEGIN
42	IF (SELECT count(*) FROM inserted) = 1
43		BEGIN
44			DECLARE @issn as char(9);
45			SELECT @issn = mgr_ssn FROM inserted;
46
47			IF (@issn) is null
48				RAISERROR('Manager Inexistent.', 16, 1);
49			ELSE
50				BEGIN 
51					IF (SELECT count(mgr_ssn) FROM COMPANY.DEPARTMENT WHERE Mgr_ssn=@issn) >= 1
52						RAISERROR('Not allowed to have employee manage multiple Departments.', 16, 1);
53					ELSE
54						IF EXISTS (SELECT * FROM deleted)
55							BEGIN
56								UPDATE COMPANY.DEPARTMENT
57								SET Mgr_ssn = @issn
58								FROM COMPANY.DEPARTMENT
59								INNER JOIN INSERTED i ON COMPANY.DEPARTMENT.Dnumber = i.Dnumber
60							END
61						ELSE 
62							BEGIN
63								INSERT INTO COMPANY.DEPARTMENT SELECT * FROM inserted; -- chamada recursiva
64							END
65				END
66		END
67END
68GO 
69
70-- d)
71
72CREATE TRIGGER Manager_Over_Employee ON COMPANY.EMPLOYEE
73AFTER INSERT, UPDATE
74AS
75BEGIN
76		DECLARE @issn as CHAR(9);
77		DECLARE @salary as DECIMAL(10,2);
78		SELECT @issn = ssn FROM inserted;
79		SELECT @salary = salary FROM inserted;
80
81		IF (@issn) is null
82			RAISERROR('Manager Inexistent.', 16, 1);
83		ELSE IF (@salary) is null OR (@salary) <= 0
84			RAISERROR('Salary Invalid.', 16, 1);
85		ELSE
86			BEGIN 
87				DECLARE @mgrSalary as DECIMAL(10,2);
88				SELECT @mgrSalary = Salary FROM COMPANY.EMPLOYEE as C WHERE C.Ssn = (SELECT DISTINCT Mgr_ssn FROM COMPANY.DEPARTMENT JOIN inserted ON COMPANY.DEPARTMENT.Dnumber = inserted.Dno);
89				IF (@mgrSalary) <= @salary
90					BEGIN
91						UPDATE COMPANY.EMPLOYEE
92						SET EMPLOYEE.Salary=@mgrSalary-1
93						WHERE COMPANY.EMPLOYEE.Ssn=@issn;
94					END
95			END
96END
97GO
98
99-- h)
100
101CREATE TRIGGER DepartmentDeleteAfter ON COMPANY.DEPARTMENT
102AFTER DELETE
103AS
104BEGIN
105	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'COMPANY' AND TABLE_NAME = 'DEPARTMENT_DELETED') 
106		CREATE TABLE COMPANY.DEPARTMENT_DELETED (
107			Dname VARCHAR(25) NOT NULL,
108			Dnumber INT NOT NULL,
109			Mgr_ssn CHAR(9),
110			Mgr_start_date DATE,
111			PRIMARY KEY (Dnumber),
112			UNIQUE (Dname) 
113		)
114
115	INSERT INTO COMPANY.DEPARTMENT_DELETED SELECT * FROM deleted;
116END
117GO 
118
119CREATE TRIGGER DepartmentDeleteInsteadOf ON COMPANY.DEPARTMENT
120INSTEAD OF DELETE
121AS
122BEGIN
123	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'COMPANY' AND TABLE_NAME = 'DEPARTMENT_DELETED') 
124		CREATE TABLE COMPANY.DEPARTMENT_DELETED (
125			Dname VARCHAR(25) NOT NULL,
126			Dnumber INT NOT NULL,
127			Mgr_ssn CHAR(9),
128			Mgr_start_date DATE,
129			PRIMARY KEY (Dnumber),
130			UNIQUE (Dname) 
131		)
132
133	INSERT INTO COMPANY.DEPARTMENT_DELETED SELECT * FROM deleted;
134
135	DECLARE @Dnumber as INT;
136	SELECT @Dnumber = Dnumber FROM deleted;
137
138	DELETE COMPANY.DEPARTMENT
139	WHERE Dnumber = @Dnumber;
140
141END
142GO 
143
144*/