Use select query over 2 joined tables in body of trigger 1

[blom0344]

I am quite positive that I am trying the impossible, but I have the request to create a trigger that writes data to a table when a status field is updated to a certain field. This is possible, but what if the data written to the table should come from two tables? Can I use a join in the body of the trigger? I am almost certain that I cannot.
I have to store information from the second table at the same time that the trigger fires to have meaningful data stored in the new table..............
Is there an alternative way to store information from two tables into a third one in case of an event in the first table?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[xrodge]

Within the body of the trigger, there are very few limitations. You can access other tables just as you would in any other pl/sql block.

The only limitation you will have is if you try to examine the table that the trigger is firing on as you will hit a mutating table error.

If you need to get data from a different table then just define a cursor and fetch from it, it shouldn't be a problem, no matter what the selection criteria.

 

[blom0344]

I may have to rephrase my question a bit to clarify.

I have table1 and table2. If the status field F_STAT is updated to a value XXX I have to write some fields to a third table. This is all quite straightforward.
However , at the same time I have to fetch some other fields from table2 and store these in the same record in the third table. Normally in SQL I use a join over these two tables over field STK_ID with additional conditions in the where clause to create a one-on-one relationship between records from both tables.

I can't get a clear picture how to get the proper data from the record in table2 by using a cursor, cause I have to fetch a series of fields from this record and combine them with fields from the record coming from table 1.

Should I use 2 additional tables to store the data in, one for fields coming from table1 and one for fields coming from table2 ??

T. Blom
Information analyst
tbl@shimano-eu.com

 

[xrodge]

Hi again,

Maybe I'm oversimplifying the problem here, but here is an example trigger that fires when the F_STAT column is updated.

[tt]CREATE OR REPLACE TRIGGER CBR_TRIGGER
AFTER UPDATE
OF F_STAT
ON TABLE1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

CURSOR c_curs IS
SELECT a.STK_ID, a.column1, b.column2, b.column3
FROM table1 a, table2 b
WHERE a.STK_ID = b.STK_ID
AND additional where clauses;

-- This cursor will store column1 from table1 and column2, column3 from the corresponding row in table2

r_curs c_curs%ROWTYPE;

BEGIN

IF :new.f_stat = 'XXX1' and ld.f_stat <> 'XXX1' -- ie it has changed to XXX1
THEN

OPEN c_curs;

FETCH c_curs INTO r_curs;

IF c_curs%FOUND THEN

update table3
set columnA = r_curs.column1,
columnB = r_curs.column2,
columnC = r_curs.column3
where STK_ID = r_curs.STK_ID;

-- You can update the third table with values selected with the cursor from either table1 or table2

END IF;

CLOSE c_curs;

END IF;

END CBR_TRIGGER;[/tt]

I don't see where there is any problem from obtaining data from any number of outside tables. The OPEN-FETCH-CLOSE cursor construct is untidy, but it is certainly the simplest way to get the data and to illustrate what I mean.

Is this what you're looking for, or is there something more difficult that I'm not picking up in your question?

 

[blom0344]

Well, that is almost exactly what I am after, so I will try to get this to work and feed back the results in any case.
In any case, I only need to do inserts into table 3, I will never have to update data within table3.
You got a star for giving me such an explicit example .................

T. Blom
Information analyst
tbl@shimano-eu.com

 

[blom0344]

Sorry, but I wonder whether the SQL part in the block will just return the combination of fields that is associated with the row in table 1 that is updated. The cursor c_curs should only contain a single row ,namely data associated with the STK_ID for which the F_STAT is updated. These tables contain millions of records, so if I run the SQL statement with additional conditions, I will still get thousands of rows if the respective STK_ID is not referenced too. Is this covered in the way the cursor is used? (Don't use PL/SQL very often as you may have guessed)

T. Blom
Information analyst
tbl@shimano-eu.com

 

[xrodge]

OK,

I was assuming that STK_ID is unique on table1 and table2.
If it's not, then we need some unique value.

if I understand correctly table1 can have many rows for each value of STK_ID
It must have some unique key value that can link the tables, we need that.

It can be referenced in the trigger as :new.unique_column (subbing in the column name for the unique_column)

So, I will assume the following table constructs


[tt]
TABLE1
Table1_Unique_Link
STK_ID
F_STAT

TABLE2
Table2_Unique_Link
STK_ID
column1
column2
column3


CREATE OR REPLACE TRIGGER CBR_TRIGGER
AFTER UPDATE
OF F_STAT
ON TABLE1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

t_STK_ID table1.STK_ID%TYPE;
t_link table1.table1_unique_link%TYPE;

CURSOR c_curs IS
SELECT column1, column2, column3
FROM table2
WHERE STK_ID = t_STK_ID
AND table2_unique_link = t_link;

-- This cursor will store column1 from table2 and column2, column3 from the corresponding row in table3
-- This unique link can be a combination of a few columns, it just has to be a unique link between the
-- triggering table and the table you want more data from.
-- Realised also, you don't need to link to the original table.
-- All the values are available as :new.column
-- You'd probably get a mutating table error anyway.

r_curs c_curs%ROWTYPE;

BEGIN

IF :new.f_stat = 'XXX1' and ld.f_stat <> 'XXX1' -- ie it has changed to XXX1
THEN

t_STK_ID := :new.STK_ID;
t_link := :new.table1_unique_link;

OPEN c_curs;

FETCH c_curs INTO r_curs;

IF c_curs%FOUND THEN

update table3
set columnA = r_curs.column1,
columnB = r_curs.column2,
columnC = r_curs.column3
where STK_ID = r_curs.STK_ID;

END IF;

CLOSE c_curs;

END IF;

END CBR_TRIGGER;[/tt]


If this doesn't help, then can you send
1. The table structures (relevant column names only)
2. The link that would uniquely select one row in table2 based upon the row being updated in table1

Don't worry about not knowing much pl/sql.
We all have to start somewhere, we'll get you through this

 

[blom0344]

To give you the precise relationship :

STK_ID IS the unique key in table 1, but not unique in table 2. However in the much tested SQL statements between these tables 2 additional conditions on table 2 ensure that for every STK_ID from table1 just one matching row from table2 qualifies:

Select ............
From table1,table2
where table1.STK_ID = table2.STK_ID AND
condition1 AND
condition2

will generate exactly one qualifying row as output for every STK_ID from table1.

Table2 is a bit of a monster storing every movement in our warehouse system where condition 1 and condition2 ensure that only movements that matter are selected.

So that I am pretty sure that the OUTCOME from the SQL-statement is one row per STK_ID. But will the cursor contain THAT specific STK_ID and related information, instead of ALL information for all STK_ID's in table 1.

Thanks for taking your time , once again.............

T. Blom
Information analyst
tbl@shimano-eu.com

 

[xrodge]

The trigger is written so that if the F_STAT column is updated, it will fire.

Therefore, using :new.STK_ID, you can access the exact value for STK_ID on the triggering line.
Therefore, where I say:

[tt]t_STK_ID := :new.STK_ID;[/tt]

this will select only the STK_ID for the line on table1 that has been updated. It will then store it in a temp variable that you can call. It can never hold the STK_ID value for any other line.

So, you can use this temp variable t_STK_ID and be assured that it has the correct unique value held within it.

 

[blom0344]

Well, this is what I defined, following your guidance:


TRIGGER TBL_TRIGGER
AFTER UPDATE OF MOV_REASON_CODE ON STOCK_MOVEMENT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
t_STK_ID STOCK_MOVEMENT.MOV_TO_LOC_ID%TYPE;
CURSOR c_curs IS
SELECT STK_ID,STK_LOC_ID,STK_PRO_CODE,STK_QTY FROM STOCK
WHERE STK_LOC_ID = t_STK_ID AND
STK_ZON_ID = 'BPI' AND STK_STAGE <> '90';
r_curs c_curs%ROWTYPE;
BEGIN
IF :new.MOV_REASON_CODE = 'RD1' and ld.MOV_REASON_CODE IS NULL
THEN
OPEN c_curs;
FETCH c_curs INTO r_curs;
IF c_curs%FOUND THEN
update TEMP_ARNO_TEST
set STK_ID = r_curs.STK_ID,
STK_LOC_ID = r_curs.STK_LOC_ID,
STK_PRO_CODE = r_curs.STK_PRO_CODE,
STK_QTY = r_curs.STK_QTY
where STK_ID = r_curs.STK_ID;
END IF;
CLOSE c_curs;
END IF;
END TBL_TRIGGER;

This compiles without errors , but unfortunately no trigger is fired if I update a record in the STOCK_MOVEMENT table that meets the requirement with regards to the MOV_REASON_CODE. Should the update part not be replaced with some sort of insert statement like:

insert into TEMP_ARNO_TEST
values(r_curs.STK_ID,r_curs.STK_LOC_ID,r_curs.STK_PRO_CODE, r_curs.STK_QTY);

??? Please advice in this respect............

T. Blom
Information analyst
tbl@shimano-eu.com