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
