Home » SQL & PL/SQL » SQL & PL/SQL » trigger not update the column (19c)
trigger not update the column [message #686268] Mon, 18 July 2022 02:35 Go to next message
mape
Messages: 282
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 #686269 is a reply to message #686268] Mon, 18 July 2022 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68292
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

General principle, you cannot modify a row that you are currently creating in another transaction as for this latter one it does not exist.

I fail to understand what you are trying to do but anyway it won't work.

With your topic ALWAYS post a COMPLETE test case to allow us to reproduce what you have.
Also ALWAYS post the COMPLETE SQL*Plus session showing what you claim.

Re: trigger not update the column [message #686270 is a reply to message #686269] Mon, 18 July 2022 07:49 Go to previous messageGo to next message
Littlefoot
Messages: 21741
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.
Re: trigger not update the column [message #686271 is a reply to message #686270] Mon, 18 July 2022 11:55 Go to previous messageGo to next message
mape
Messages: 282
Registered: July 2006
Location: Slovakia
Senior Member
thanks a lot for your comments
Now I get it I cannot modify a row that I am currently creating.
Re: trigger not update the column [message #686350 is a reply to message #686271] Fri, 12 August 2022 08:35 Go to previous message
Frank
Messages: 7896
Registered: March 2000
Senior Member
Late to the party, but that is a great naming convention you have there. Perfect to totally confuse the rest of the world Wink
Previous Topic: Audit Database Problem
Next Topic: Retrieve last month running total when current month is null
Goto Forum:
  


Current Time: Tue Oct 04 05:08:37 CDT 2022