Re: Writing a delete trigger

cjg2_at_student.open.ac.uk wrote:
>
> I am wanting to write a delete trigger that when fired on table a looks to
> see if what the current highest value of a certain column is after the delete
> and if it has changed (ie the row deleted contain that value) a update is
> made to table b with value from the row which now contains this new highest
> value.
>
> since the trigger cannot select from the table that fired it does anyone have any
> good ideas?

If you make the trigger fire AFTER DELETE on table a, you can select your max value from table a and insert it into table b with no problem.

Example: Table A: id (varchar2(10), salary (number)

         Table B: max_salary

CREATE TRIGGER update_b
AFTER DELETE ON table_a
DECLARE
dummy INTEGER;
BEGIN
SELECT MAX(salary)
INTO dummy
FROM table_a;
INSERT INTO table_b (max_salary)
VALUES (dummy);
END; I think this will do what you want.

Regards,
Vince Received on Thu Apr 25 1996 - 00:00:00 CEST