| Trigger Creation Help [message #22655] |
Mon, 21 October 2002 11:25  |
Michele
Messages: 77
Registered: December 2000
|
Member |
|
|
Hello everyone,
I have little experience when writing trigger bodies, so I need your help. I posted this up on the newbies site as well.
I have a table called Change_Data that stores the table name and column name of tables that were updated. Ex:
Pers_id Tab_name Col_name
1 Cntrctr rep_status
1 Cntrctr gender
What I need to do is set up a trigger(see below) that inserts values into a Temp_table. Since the col_name value is dynamic(as well as datatype of the column),I am running into a problem with writing a valid statement. I put question marks where I am unsure. Basically, the col_name tells me what column changed on the Cntrctr table. I then need to take that value and use it to search against the Cntrctr to get the true value. Ex of Cntrctr:
Pers_id first_name last_name gender rep_status
1 Donald Duck Male Rookie
Here is my trigger so far, you can ignore the first if statement since I am not having a trouble with it.
CREATE OR REPLACE TRIGGER CRYSTAL_TEMP_INS AFTER INSERT ON CHANGE_DATA FOR EACH ROW
Declare
pk number(8);
ADDRESS_REC WRK.ADDRESS%ROWTYPE;
CNTRCTR_REC WRK.CNTRCTR%ROWTYPE;??????
BEGIN
select CRYSTAL_TEMP_SEQ.nextval into pk from dual;
IF :new.tab_name = 'ADDRESS' THEN
select * into ADDRESS_REC from WRK.ADDRESS A where A.pers_id = :new.pers_id;
insert into WRK.CRYSTAL_TEMP (temp_id,pers_id,data_values)
values(pk,:new.pers_id,ADDRESS_REC.street1||ADDRESS_REC.street2 and so on.....);
END IF;
IF :new.tab_name = 'CNTRCTR' THEN
select ? into CNTRCTR_REC from WRK.CNTRCTR C where C.pers_id=:new.pers_id;
insert into WRK.CRYSTAL_TEMP(temp_id,pers_id,data_values)
values(pk,:new.pers_id,?);
END IF;
END;
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
