Triggers, procedures and inserting into a table

MGZ · #1 (**permalink**) 11-13-02, 16:13

hello

I have a table that currently has a trigger on it.

Code:

CREATE trigger update_host
    BEFORE UPDATE ON host FOR EACH row
		EXECUTE PROCEDURE host_hist_ins;

What I want to happen is this:

When the trigger goes off, it takes the current record that is being updated from the host table, and insert it into the host_hist table.

The problem I am having is how do I write it so it is parsing the value of the ID (Primary key) into the code ?

Code:

...
DECLARE
  cursor a_cur IS SELECT host_id FROM host where id = [this is the variable I want from the update]   
BEGIN
...

Hopefully that makes sense

thanks

andrewst · #2 (**permalink**) 11-13-02, 16:41

In the trigger, you will have to pass the ID to the procedure. The ID will be :NEW.ID

CREATE trigger update_host
BEFORE UPDATE ON host FOR EACH row
BEGIN
host_hist_ins( :NEW.ID );
END;

The procedure requires a parameter to receive the value:

PROCEDURE host_hist_ins ( p_id IN host.id%TYPE ) IS
BEGIN
...
END;

MGZ · #3 (**permalink**) 11-13-02, 17:03

heh after some reading I found the :new and

ld :-) I have to get my mind out of parsing variables to web pages.

thanks for the help.

Internet Services

Web Development

Digital Marketing

Consumer Tech