trigger not update the column [message #686268] |
Mon, 18 July 2022 02:35 |
mape
Messages: 298 Registered: July 2006 Location: Slovakia
|
Senior Member |
|
|
Hello
I don't know what Iam doing wrong, because after insert record into the table, trigger does not fill in the value.
insert into INT_TABLE(month, team,DESCRIPTION, time_stamp, CREATED_BY )
values( to_date('01.06.2090','dd.mm.yyyy'), 'Team 2 ZA', 'mape', sysdate,'mape.mape');
CREATE OR REPLACE TRIGGER TRG_AFTER_EMP_INT_EXTRA_AKTIVITY
BEFORE INSERT ON INT_TABLE
REFERENCING NEW AS newRow OLD AS oldRow
FOR EACH ROW
DECLARE
v_dept number;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
select DEPARTMENT_KEY into v_dept
from INP_DEPARTMENTS
where 1=1
and DEPARTMENT_NAME=:newRow.TEAM
and valid_flag='Y';
UPDATE INT_TABLE
SET DEPARTMENT_KEY = v_dept
WHERE 1=1--
AND team=:newRow.team
AND to_char(time_stamp, 'yyyymmdd') = to_char(sysdate, 'yyyymmdd')
and DEPARTMENT_KEY is null;
commit;
END TRG_AFTER_EMP_INT_EXTRA_AKTIVITY;
Does anybody know where is the problem ??
Thanks a lot
Martin
|
|
|
|
Re: trigger not update the column [message #686270 is a reply to message #686269] |
Mon, 18 July 2022 07:49 |
|
Littlefoot
Messages: 21811 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
To me, it looks as if you hit the mutating table error and tried to "fix" it using autonomous transaction.
If so, well, that's most often wrong way to solve a problem. I'd try with something like this (as you can see, quite simpler than your code):
CREATE OR REPLACE TRIGGER trg_after_emp_int_extra_aktivity
BEFORE INSERT
ON int_table
FOR EACH ROW
BEGIN
SELECT department_key
INTO :new.department_key --> select directly into new row's department key
FROM inp_departments
WHERE department_name = :new.team
AND valid_flag = 'Y';
END trg_after_emp_int_extra_aktivity;
On the other hand, as you didn't post test case (re-read Michel's objection), I can't tell whether data model you use is correct or not. Code you posted suggests that tables involved might be in a master-detail relationship. If so, then you have foreign key constraint set. It should point from detail table's column to a primary (or unique) key column of the master table.
As you're having "where department_name = :new.team", i.e. use names, it looks as if you're actually trying to fetch the primary key column value into the detail table, using its name. That's more or less just the opposite of what you should be doing.
But, once again, without some more info, it is difficult to tell what you have and what you should do.
|
|
|
|
|