Re: Passing a WHERE clause by trigger to a function

[Melvin Call <melvincall979@xxxxxxxxx>]

Oops, I meant to add the test code to the end of my request a few minutes ago. The last four lines are the relevant ones, and I want the final delete to fail. Everything else is just setting things up:

DROP TABLE IF EXISTS person;
CREATE TABLE person (
   person_id INTEGER NOT NULL,
   person_firstname TEXT NOT NULL,
   person_lastname TEXT NOT NULL,
   primary key (person_id)
);

DROP TABLE IF EXISTS company;
CREATE TABLE company (
   company_id INTEGER NOT NULL,
   company_name TEXT NOT NULL,
   company_address TEXT,
   company_city TEXT,
   company_state TEXT,
   company_zip VARCHAR(9),
   primary key (company_id)
);

DROP TABLE IF EXISTS department;
CREATE TABLE department (
   department_id INTEGER NOT NULL,
   department_name TEXT NOT NULL,
   company_id INTEGER NOT NULL
      REFERENCES company(company_id),
   primary key (department_id)
);

DROP TABLE IF EXISTS role;
CREATE TABLE role (
   role_id INTEGER NOT NULL,
   role_title TEXT NOT NULL,
   role_description TEXT,
   primary key (role_id)
);

DROP TABLE IF EXISTS staff;
CREATE TABLE staff (
   staff_id INTEGER NOT NULL
      REFERENCES person(person_id),
   department_id INTEGER NOT NULL
      REFERENCES department(department_id),
   role_id INTEGER NOT NULL
      REFERENCES role(role_id),
   primary key (staff_id)
);

DROP VIEW IF EXISTS staffrole;
CREATE VIEW staffrole AS
   SELECT person_lastname AS last,
          person_firstname AS first,
          company_name AS company,
          department_name AS dept,
          role_title AS title
   FROM person
      JOIN staff ON staff.staff_id = person.person_id
      JOIN department ON department.department_id = staff.department_id
      JOIN company ON company.company_id = department.company_id
      JOIN role ON role.role_id = staff.role_id;

CREATE OR REPLACE FUNCTION update_staff()
RETURNS TRIGGER AS $$
   BEGIN
      IF (TG_OP = 'DELETE') THEN
         DELETE FROM staff
         WHERE staff_id =
            (SELECT person_id
             FROM person
             WHERE person_lastname = OLD.last
             AND person_firstname = OLD.first);
         IF NOT FOUND THEN RETURN NULL; END IF;

         RETURN OLD;
      END IF;
   END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_staff
   INSTEAD OF DELETE ON staffrole
   FOR EACH ROW EXECUTE PROCEDURE update_staff();

INSERT INTO person (person_id, person_firstname, person_lastname) VALUES
   (1, 'John', 'Doe'),
   (2, 'Jim', 'Doe2'),
   (3, 'Joe', 'Doe3'),
   (4, 'Jeb', 'Doe4'),
   (5, 'Jack', 'Doe5'),
   (6, 'Jerry', 'Doe6'),
   (7, 'Jen', 'Doe7');

INSERT INTO company (company_id, company_name, company_address, company_city, company_state, company_zip) VALUES
   (1, 'Company1', '123 Any Street', 'Your Town', 'TX', '787348438'),
   (2, 'Company2', '456 Different Street', 'My Town', 'OH', '443139342');

INSERT INTO department (department_id, department_name, company_id) VALUES
   (1, 'Accounting', (SELECT company_id FROM company WHERE company_name = 'Company1')),
   (2, 'HR', (SELECT company_id FROM company WHERE company_name = 'Company1')),
   (3, 'Purchasing', (SELECT company_id FROM company WHERE company_name = 'Company1')),
   (4, 'Maintenance', (SELECT company_id FROM company WHERE company_name = 'Company1')),
   (5, 'IT', (SELECT company_id FROM company WHERE company_name = 'Company1')),
   (6, 'Accounting', (SELECT company_id FROM company WHERE company_name = 'Company2')),
   (7, 'HR', (SELECT company_id FROM company WHERE company_name = 'Company2')),
   (8, 'Purchasing', (SELECT company_id FROM company WHERE company_name = 'Company2')),
   (9, 'Maintenance', (SELECT company_id FROM company WHERE company_name = 'Company2')),
   (10, 'IT', (SELECT company_id FROM company WHERE company_name = 'Company2'));

INSERT INTO role (role_id, role_title, role_description) VALUES
   (1, 'Manager', 'Supervisor'),
   (2, 'Accountant', 'Bookkeeper'),
   (3, 'Electrician', 'Power broker'),
   (4, 'Specialist', 'HR guru'),
   (5, 'Clerk', 'Payroll person'),
   (6, 'Admin Aid', 'Real boss'),
   (7, 'CEO', 'Money bags'),
   (8, 'Grounds keeper', 'Grass man'),
   (9, 'System admin', 'IT Guru'),
   (10, 'Receptionist', 'Greeter');

INSERT INTO staff (staff_id, department_id, role_id) VALUES
   ((SELECT person_id FROM person WHERE person_lastname = 'Doe2'),
    (SELECT department_id FROM department WHERE department_name = 'Accounting' AND
     company_id = (SELECT company_id FROM company WHERE company_name = 'Company1')),
    (SELECT role_id FROM role WHERE role_title = 'Manager')),
   ((SELECT person_id FROM person WHERE person_lastname = 'Doe3'),
    (SELECT department_id FROM department WHERE department_name = 'Accounting' AND
     company_id = (SELECT company_id FROM company WHERE company_name = 'Company2')),
    (SELECT role_id FROM role WHERE role_title = 'Manager')),
   ((SELECT person_id FROM person WHERE person_lastname = 'Doe4'),
    (SELECT department_id FROM department WHERE department_name = 'IT' AND
     company_id = (SELECT company_id FROM company WHERE company_name = 'Company1')),
    (SELECT role_id FROM role WHERE role_title = 'System admin')),
   ((SELECT person_id FROM person WHERE person_lastname = 'Doe5'),
    (SELECT department_id FROM department WHERE department_name = 'IT' AND
     company_id = (SELECT company_id FROM company WHERE company_name = 'Company2')),
    (SELECT role_id FROM role WHERE role_title = 'System admin')),
   ((SELECT person_id FROM person WHERE person_lastname = 'Doe6'),
    (SELECT department_id FROM department WHERE department_name = 'Maintenance' AND
     company_id = (SELECT company_id FROM company WHERE company_name = 'Company1')),
    (SELECT role_id FROM role WHERE role_title = 'Electrician')),
   ((SELECT person_id FROM person WHERE person_lastname = 'Doe7'),
    (SELECT department_id FROM department WHERE department_name = 'Maintenance' AND
     company_id = (SELECT company_id FROM company WHERE company_name = 'Company2')),
    (SELECT role_id FROM role WHERE role_title = 'Electrician'));

DELETE FROM staffrole WHERE last='Doe5';
DELETE 1

DELETE FROM staffrole WHERE company = 'Company1';
DELETE 3

On Mon, Jun 3, 2013 at 1:55 PM, Melvin Call <melvincall979@xxxxxxxxx> wrote:

Hello list,

I am playing around with views and INSTEAD OF triggers, and have run across an interesting problem that I can't seem resolve, so I figured it was time to expose my ignorance to the whole world.

I have several tables:
person, consisting of a person's name and address, with a person_id as PK
company, consisting of a company name and address, with a company_id as PK
department, consisting of a department name and address, with a department_id as PK, and an FK to it's associated company
role, consisting of a job title and description, with role_id as PK
staff, consisting of a person_id FK from person as the PK, a FK to their role, and a FK to their department

I've created a view that joins the various tables to give the person's name, the department that employs them, and the company that the department is part of. I want to be able to delete a person entry from the staff table through the view, and I have figured that out with a procedure that is called by an INSTEAD OF trigger:

IF (TG_OP = 'DELETE') THEN
   DELETE FROM staff
   WHERE person_id =
      (SELECT person_id
       FROM person
       WHERE person_lastname = OLD.last AND person_firstname = OLD.first);

Where OLD.last and OLD.first are the view column aliases.

So the following statement will delete from just the staff table the row associated with the person_id, and will delete everything from the view too:
DELETE FROM staff
WHERE last = 'Doe' AND first = 'John';

This deletes the single record for John Doe (knowing it would delete multiples if there were multiple John Doe in the table).

But, if I issue the following statement:
DELETE FROM staff
WHERE company_name = 'company1';

all staff records associated with company1 are deleted. I want the first statement to succeed, but the second to fail in such a way that I can capture it and handle it. Is it possible that when the trigger is fired to pass to the function the WHERE clause from the DELETE statement, or something along that line? Or am I looking at this problem all wrong?

Thanks,
Melvin