ALTER TABLE employees ALTER COLUMN job_id DROP NOT NULL ALTER TABLE em

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
ALTER TABLE employees ALTER COLUMN job_id DROP NOT NULL;
ALTER TABLE employees ALTER COLUMN email DROP NOT NULL;
CREATE VIEW seattle_employees AS
SELECT e.first_name, e.last_name, e.hire_date, e.salary, d.department_name FROM
employees e, departments d, locations l, countries c
WHERE e.department_id=d.department_id AND
l.location_id=d.location_id AND
l.country_id=c.country_id AND
c.country_name='United States of America' AND
l.city='Seattle';
CREATE OR REPLACE FUNCTION e_u_if()
RETURNS trigger AS $$
DECLARE
res RECORD;
BEGIN
SELECT count(*) as _count, department_id as depId into res from
departments d, locations l, countries c
WHERE
l.location_id=d.location_id AND
l.country_id=c.country_id AND
c.country_name='United States of America' AND
l.city='Seattle' AND
NEW.department_name = d.department_name
GROUP BY d.department_id;
if res._count > 0 then
INSERT INTO employees (first_name, last_name, hire_date, salary, department_id)
VALUES (
new.first_name, new.last_name, new.hire_date, new.salary, res.depId);
RETURN new;
END IF;
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL SECURITY DEFINER;
CREATE TRIGGER e_u_if INSTEAD OF insert on
seattle_employees FOR EACH ROW EXECUTE
PROCEDURE e_u_if();