Create Trigger on update and insert is not working

Toad for Oracle
1

Hi ,

I have posted my query but for some reason this errors out

CREATE TRIGGER z_LOGIN2 AFTER UPDATE OF lastsignondttm ON SYSADM.PSOPRDEFN
FOR EACH ROW

BEGIN
SELECT DISTINCT a.oprid, a.oprdefndesc, a.lastsignondttm,a.lastupddttm,a.lastupdoprid, b.rolename
from SYSADM.psoprdefn a, SYSADM.psroleuser b
where a.oprid=b.roleuser
and (rolename = ‘FAS Change Admin’ or b.rolename=‘PeopleSoft Administrator’)

INSERT INTO SYSADM.PS_FZAP_AUDIT_LOGS VALUES (a.oprid,a.oprdefndesc,a.lastsignondttm,‘test’,‘FS9DEVP’,user,sysdate);

2

If you do a select in a trigger, you need to select the data into a variable. You can’t just do a select like that and then reference it in a later statement.

I am guessing that you don’t even need the select. you can reference the values of the newly updated row with the :new variable, like this:

CREATE OR REPLACE TRIGGER emp1_ins

AFTER UPDATE

ON EMP1

REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

BEGIN

INSERT INTO EMP2 (

EMPNO, ENAME, JOB,

MGR, HIREDATE, SAL,

COMM, DEPTNO)

VALUES ( :new.EMPNO,

:new.ENAME,

:new.JOB,

:new.MGR,

:new.HIREDATE,

:new.SAL,

:new.COMM,

:new.DEPTNO );

EXCEPTION

WHEN OTHERS THEN

– Consider logging the error and then re-raise

RAISE;

END emp1_ins;

/

3

Hi John,

Thanks for your reply. However i need a select here because when there is a update of the filed “lastsignondttm” in the table - PSOPRDEFN , i need to insert values into my Z table only when the oprid has roles “z” or roles “Y” in the PSROLEUSER TABLE.

SO i tried a TRIGGER Query like this, But this also seems to fail in the insert

CREATE OR REPLACE TRIGGER z_LOGIN2

AFTER UPDATE OF lastsignondttm

ON SYSADM.PSOPRDEFN

REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

BEGIN

IF (SELECT COUNT(1)

FROM SYSADM.psroleuser b

WHERE b.roleuser = NEW.oprid AND (b.rolename = ‘FAS Change Admin’

OR b.rolename = ‘PeopleSoft Administrator’)) > 0 THEN

INSERT INTO z_LOGIN VALUES (:NEW.oprid,:NEW.oprdefndesc,:NEW.lastsignondttm,‘test’,‘FS9DEVP’,:NEW.lastupddttm,:NEW.lastupdoprid);

END IF;

Please share ur ideas.

Thanks

4

Why not do a simple insert select?

INSERT INTO z_LOGIN VALUES (:NEW.oprid,:NEW.oprdefndesc,:NEW.lastsignondttm,‘test’,‘FS9DEVP’,:NEW.lastupddttm,:NEW.lastupdoprid);

Select columns_to_be_passed to_insert

 FROM SYSADM.psroleuser b

 WHERE b.roleuser = NEW.oprid AND (b.rolename = 'FAS Change Admin'

       OR b.rolename = 'PeopleSoft Administrator')) > 0

From: winona [mailto:bounce-winona@toadworld.com]

Sent: Tuesday, December 03, 2013 9:15 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Create Trigger on update and insert is not working

RE: Create Trigger on update and insert is not working

Reply by winona

Hi John,

Thanks for your reply. However i need a select here because when there is a update of the filed “lastsignondttm” in the table - PSOPRDEFN , i need to insert values into my Z table only when
the oprid has roles “z” or roles “Y” in the PSROLEUSER TABLE.

SO i tried a TRIGGER Query like this, But this also seems to fail in the insert

CREATE OR REPLACE TRIGGER z_LOGIN2

AFTER UPDATE OF lastsignondttm

ON SYSADM.PSOPRDEFN

REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

BEGIN

IF (SELECT COUNT(1)

 FROM SYSADM.psroleuser b

 WHERE b.roleuser = NEW.oprid AND (b.rolename = 'FAS Change Admin'

       OR b.rolename = 'PeopleSoft Administrator')) > 0 THEN

INSERT INTO z_LOGIN VALUES (:NEW.oprid,:NEW.oprdefndesc,:NEW.lastsignondttm,‘test’,‘FS9DEVP’,:NEW.lastupddttm,:NEW.lastupdoprid);

END IF;

Please share ur ideas.

Thanks

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

5

Hi Bret,

I tried the above select insert like below

INSERT INTO z_LOGIN VALUES (:NEW.oprid,:NEW.oprdefndesc,:NEW.lastsignondttm,‘test’,‘FS9DEVP’,:NEW.lastupddttm,:NEW.lastupdoprid);

Select COUNT(1)

FROM SYSADM.psroleuser b

WHERE b.roleuser = NEW.oprid AND (b.rolename = ‘FAS Change Admin’

OR b.rolename = ‘PeopleSoft Administrator’)) > 0

Note : columns_to_be_passed to_insert -> there are present in psoprdefn table not in psroleuser table.

The first error is" Parameter ‘oprid’ not found "and the next one is "ORA-00911: invalid character’

6

My syntax was not correct – I was trying to show you the right direction. Here’s an example of what I mean:

insert into berts_table (col_1, col_2, col_3, col_4)

select from another_table (col_a, col_b, col_c, col_d)

where ….

No variables necessary ….

Now apply this kind of approach to your two tables …

7

Which ORA- error message are you getting?

The one about “mutating tables”?

8

I tried the approach as you mentioned above

INSERT INTO SYSADM.PS_FZAP_AUDIT_LOGS (oprid,oprdefndesc,lastsignondttm,

reasondescr,dbname,lastupddttm,lastupdoprid)

select distinct a.oprid, a.oprdefndesc, a.lastsignondttm,a.lastupddttm,a.lastupdoprid,b.rolename

from sysadm.psoprdefn a, sysadm.psroleuser b

where a.oprid=b.roleuser

and (rolename = ‘FAS Change Admin’ or b.rolename=‘PeopleSoft Administrator’)

Select when executed alone is working fine. the select result has 6 columns and from which i need 5 columns to be inserted in my Z table and apart from that 2 extra column “reasondescr,dbname” (hardcoded)should also be inserted so i need to have variables .

Thanks pls share ur ideas

9

No i dont get mutating error message but got " Parameter ‘oprid’ not found "and the next one is "ORA-00911: invalid character’ But now with the above approach i dont get the errors but i am not able to insert into my z table

10

how about this? (I had to add an extra value to your insert statement because ‘reasondesc’ was not in the select)

CREATE OR REPLACE TRIGGER z_LOGIN2

AFTER UPDATE OF lastsignondttm

ON SYSADM.PSOPRDEFN

REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

BEGIN

INSERT INTO SYSADM.PS_FZAP_AUDIT_LOGS (oprid, oprdefndesc,lastsignondttm,

reasondescr,dbname,lastupddttm,lastupdoprid)

select distinct a.oprid, a.oprdefndesc, a.lastsignondttm, a.lastupddttm, ‘reasondescr’, a.lastupdoprid,b.rolename

from sysadm.psoprdefn a, sysadm.psroleuser b

where a.oprid=b.roleuser

and (rolename = ‘FAS Change Admin’ or b.rolename=‘PeopleSoft Administrator’);

END;

/

11

Hi John,

i was almost there ,

I executed the insert and select first alone standalone and i got the data inserted into my Z table and then when executed the whole code i got the below error.

ORA-24344: success with compilation error

The trigger is enabled.

12

Find the trigger in the Schema Browser, go to the Errors tab to see what the error is.

My guess is that you are creating it in a different schema and you need to make some grants on the tables to the schema that owns the trigger. Another option would be to put the trigger in the SYSADM schema.

13

Since i create trigger on PSOPRDEFN table which is a standard table and i dont have persmisson to edit it am i getting this compilation error?? Moreover in the error tab the message is

PL/SQL : ORA : 00942:table or view does not exist in line 5 column 34.

line 5 : ON sysadm.psoprdefn

14

That means that the schema that you are creating the trigger in (this is the username that you logged in with) does not have privileges on the SYSADM.PSOPRDEFN table.

The simplest solution is to create the trigger in the SYSADM schema…so the first line of your ‘create trigger’ statement would look like this:

CREATE OR REPLACE TRIGGER SYSADM.z_LOGIN2…

You should also drop the trigger that you already created.

15

Hi I have one more doubt

When i try to login to my App Designer i get the below message

File: E:\pt85109b-retail\peopletools\src\pssys\stmupd.cppSQL error. Stmt #: 2291 Error Position: 7 Return: 4091 - ORA-04091: table SYSADM.PSOPRDEFN is mutating, trigger/function may not see it ORA-06512: at “SYSADM.Z_LOGIN2”, line 2 ORA-04088: error during execution of trigger ‘SYSADM.Z_LOGIN2’

Failed SQL stmt:UPDATE PSOPRDEFN SET LASTSIGNONDTTM = TO_TIMESTAMP(:1,‘YYYY-MM-DD-HH24.MI.SS.FF’) WHERE OPRID = :2

However in toad this executed without any mistake.

16

Oh, yeah that’s happening because in the trigger you are selecting from the table that is in the middle of an update.

Are you just interested in the row being updated? If so, I think you can change your trigger to this (I did not test it):

CREATE OR REPLACE TRIGGER SYSADM.z_LOGIN2

AFTER UPDATE OF lastsignondttm

ON SYSADM.PSOPRDEFN

REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

BEGIN

INSERT INTO SYSADM.PS_FZAP_AUDIT_LOGS (oprid, oprdefndesc,lastsignondttm,

reasondescr,dbname,lastupddttm,lastupdoprid)

select distinct :new.oprid, :new.oprdefndesc, :new.lastsignondttm, :new.lastupddttm, ‘reasondescr’,

:new.lastupdoprid,b.rolename

from sysadm.psroleuser b

where b.roleuser = :new.oprid

and (b.rolename = ‘FAS Change Admin’ or b.rolename=‘PeopleSoft Administrator’);

END;

/

17

Thanks a lot for ur time and help. But when i execute the above code it prompts for values for variables like oprid , oprdefndesc… and when i enter and click oky it pops out error .

Parameter ‘oprid’ not found

ORA-00911: invalid character

CREATE OR REPLACE TRIGGER SYSADM.z_LOGIN2

AFTER UPDATE OF lastsignondttm

ON SYSADM.PSOPRDEFN

REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

BEGIN

INSERT INTO SYSADM.PS_FZAP_AUDIT_LOGS (oprid, oprdefndesc,lastsignondttm,

reasondescr,dbname,lastupddttm,lastupdoprid)

select distinct :new.oprid,:new.oprdefndesc,:new.lastsignondttm,‘reasondescr’,‘FSPDEVP’,:new.lastupddttm,:new.lastupdoprid

from sysadm.psroleuser b

where b.roleuser = :new.oprid

and (b.rolename = ‘FAS Change Admin’ or b.rolename=‘PeopleSoft Administrator’);

END;

18

You can’t execute that insert statement separately. It is only valid within the trigger.

19

Yes i am aware that it is valid within the trigger , but i get an error when i execute the whole trigger query

Parameter ‘oprid’ not found

ORA-00911: invalid character

But when i go n check in the errors tabs in Schema there are no errors and in the column tab the arrow is poiting to OPRID, is it the same authorisation issue for my usedid?

20

I am not sure what’s going wrong. I just build this script to create the tables and trigger, insert some rows and make the trigger fire with an update statement. It all works fine for me.

I am running this script with F5 in Toad’s editor.

drop table PSOPRDEFN;

drop table psroleuser;

drop table PS_FZAP_AUDIT_LOGS;

create table PSOPRDEFN

(oprid varchar2(30),

oprdefndesc varchar2(30),

lastsignondttm varchar2(30),

lastupddttm varchar2(30),

lastupdoprid varchar2(30));

create table psroleuser

(rolename varchar2(30),

roleuser varchar2(30));

create table PS_FZAP_AUDIT_LOGS

(oprid varchar2(30),

oprdefndesc varchar2(30),

lastsignondttm varchar2(30),

reasondescr varchar2(30),

dbname varchar2(30),

lastupddttm varchar2(30),

lastupdoprid varchar2(30));

CREATE OR REPLACE TRIGGER z_LOGIN2

AFTER UPDATE OF lastsignondttm

ON PSOPRDEFN

REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

BEGIN

INSERT INTO PS_FZAP_AUDIT_LOGS (oprid, oprdefndesc,lastsignondttm,

reasondescr,dbname,lastupddttm,lastupdoprid)

select distinct :new.oprid,:new.oprdefndesc,:new.lastsignondttm,‘reasondescr’,‘FSPDEVP’,:new.lastupddttm,:new.lastupdoprid

from psroleuser b

where b.roleuser = :new.oprid

and (b.rolename = ‘FAS Change Admin’ or b.rolename=‘PeopleSoft Administrator’);

END;

/

Insert into JDORLON.PSROLEUSER

(ROLENAME, ROLEUSER)

Values

(‘FAS Change Admin’, ‘1’);

Insert into PSOPRDEFN

(OPRID, OPRDEFNDESC, LASTSIGNONDTTM, LASTUPDDTTM, LASTUPDOPRID)

Values

(‘1’, ‘b’, ‘d’, ‘d’, ‘e’);

COMMIT;

update PSOPRDEFN set lastsignondttm = ‘x’ where oprid = ‘1’;

select * from PS_FZAP_AUDIT_LOGS;