CODEWITHSUNDEEP
oracle-databasetriggersinsert
Marcus
Marcus

Reputation: 3869

Oracle trigger not inserting values in another table

I have SOAP_SERVICE table in which if new value gets inserted into this table then the values should be insert into SOAP_SERVICE_STATUS table also. I am writing below trigger for this which does not have any error and compiled successfully. But when i tried to insert value into SOAP_SERVICE table then the value is not getting inserted into SOAP_SERVICE_STATUS table.

create or replace TRIGGER RATOR_MONITORING.TRG_TRK_SOAP_SERVICE_STATUS
  AFTER INSERT 
 ON RATOR_MONITORING_CONFIGURATION.SOAP_SERVICE
 FOR EACH ROW
BEGIN
   INSERT INTO RATOR_MONITORING.SOAP_SERVICE_STATUS (SOAP_SERVICE_STATUS_ID,SOAP_SERVICE_ID,STATUS)
       VALUES (SOAP_SERVICE_STATUS_SEQ.nextval,:new.SOAP_SERVICE_ID,'N');
END;

Upvotes: 0

Views: 1129

Answers (2)

Luke Woodward
Luke Woodward

Reputation: 65044

Sorry, cannot reproduce this one:

SQL> CREATE USER RATOR_MONITORING IDENTIFIED BY "password"
2            DEFAULT TABLESPACE USERS;

User created.

SQL> CREATE USER RATOR_MONITORING_CONFIGURATION IDENTIFIED BY "password"
2            DEFAULT TABLESPACE USERS;

User created.

SQL> GRANT CONNECT TO RATOR_MONITORING, RATOR_MONITORING_CONFIGURATION;

Grant succeeded.

SQL> GRANT CREATE TABLE TO RATOR_MONITORING, RATOR_MONITORING_CONFIGURATION;

Grant succeeded.

SQL> GRANT CREATE SEQUENCE TO RATOR_MONITORING;

Grant succeeded.

SQL> GRANT CREATE ANY TRIGGER TO RATOR_MONITORING;

Grant succeeded.

SQL> ALTER USER RATOR_MONITORING QUOTA UNLIMITED ON USERS;

User altered.

SQL> ALTER USER RATOR_MONITORING_CONFIGURATION QUOTA UNLIMITED ON USERS;

User altered.

SQL> CONNECT RATOR_MONITORING_CONFIGURATION/password
Connected.
SQL> CREATE TABLE RATOR_MONITORING_CONFIGURATION.SOAP_SERVICE (SOAP_SERVICE_ID INTEGER);

Table created.

SQL> CONNECT RATOR_MONITORING/password
Connected.
SQL> CREATE TABLE RATOR_MONITORING.SOAP_SERVICE_STATUS
2              (SOAP_SERVICE_STATUS_ID INTEGER, SOAP_SERVICE_ID INTEGER, STATUS CHAR(1));

Table created.

SQL> CREATE SEQUENCE RATOR_MONITORING.SOAP_SERVICE_STATUS_SEQ;

Sequence created.

SQL> create or replace TRIGGER RATOR_MONITORING.TRG_TRK_SOAP_SERVICE_STATUS
2       AFTER INSERT
3      ON RATOR_MONITORING_CONFIGURATION.SOAP_SERVICE
4      FOR EACH ROW
5     BEGIN
6        INSERT INTO RATOR_MONITORING.SOAP_SERVICE_STATUS (SOAP_SERVICE_STATUS_ID,SOAP_SERVICE_ID,STATUS)
7            VALUES (SOAP_SERVICE_STATUS_SEQ.nextval,:new.SOAP_SERVICE_ID,'N');
8     END;
9     /

Trigger created.

SQL> CONNECT RATOR_MONITORING_CONFIGURATION/password
Connected.
SQL> INSERT INTO RATOR_MONITORING_CONFIGURATION.SOAP_SERVICE (SOAP_SERVICE_ID) VALUES (7);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> CONNECT RATOR_MONITORING/password
Connected.
SQL> SELECT * FROM RATOR_MONITORING.SOAP_SERVICE_STATUS;

SOAP_SERVICE_STATUS_ID SOAP_SERVICE_ID S
---------------------- --------------- -
                     1               7 N

Notes:

  1. The CREATE ANY TRIGGER privilege is necessary for RATOR_MONITORING to create a trigger on a table in another schema,
  2. When we change connection in SQL*Plus, the database creates a new session for us to use. Changes made in one session are not visible in another session until these changes are committed.
  3. SQL*Plus implicitly commits transactions when disconnecting. In the above example, we commit explicitly before the last CONNECT so that we are not relying on implicit behaviour.

Upvotes: 2

Marmite Bomber
Marmite Bomber

Reputation: 21115

The most probable explanation is that you select the table RATOR_MONITORING.SOAP_SERVICE_STATUS with a different connection without COMMITing the original session performing the insert in RATOR_MONITORING_CONFIGURATION.SOAP_SERVICE. After the commit the record should be visible.

Note also that this is a side-effect of the fact that the trigger is in a different schema than the table; the user RATOR_MONITORING_CONFIGURATION can insert in the table RATOR_MONITORING.SOAP_SERVICE_STATUS even if he/she doen't have INSERT grant!

To enable this the user RATOR_MONITORING must have the privilege create any trigger - which is not always considered as best practice (as to mighty privilege) - see eg Tom Kyte

Upvotes: 1

Related Questions