Home » SQL & PL/SQL » SQL & PL/SQL » Trigger on Table (4 merged) (Oracle 19C database)
Trigger on Table (4 merged) [message #683768] Fri, 19 February 2021 15:06 Go to next message
ettentrala
Messages: 6
Registered: August 2008
Location: Maryland
Junior Member
Hello,

It would be greatly appreciated if I could get some assistance on creating a trigger on a table. I have a table called SPRIDEN. When a row is entered, the trigger inserts only the new row(S) in the SPRIDEN_CHANGES table. This is working fine. However, I have another table that is GORIROL that I would like to include the GORIROL_ROLE to the SPRIDEN_CHANGES table. Is this possible?

This works....

CREATE OR REPLACE TRIGGER log_rows_spriden
  AFTER INSERT ON SATURN.spriden
  FOR EACH ROW
BEGIN
  INSERT INTO SATURN.spriden_changes
            VALUES
         (
             :new.spriden_pidm,
             :new.spriden_id,
             :new.spriden_last_name,
             :new.spriden_first_name,
             :new.spriden_mi,
             :new.spriden_create_user,
             :new.spriden_create_date
         );
END log_rows_spriden;
/

I tried to add the other table and it doesn't work. What am I doing wrong? This is my first time writing a trigger.

CREATE OR REPLACE TRIGGER log_rows_spriden
  AFTER INSERT ON 
   (SATURN.spriden  JOIN GENERAL.GORIROL
      ON
	  SPRIDEN_PIDM = GORIROL_PIDM
	  WHERE GORIROL_ROLE IN ('STUDENT','ADMITTED','APPLICANT'))
  FOR EACH ROW
BEGIN
  INSERT INTO SATURN.spriden_changes
            VALUES
         (
             :new.spriden_pidm,
             :new.spriden_id,
             :new.spriden_last_name,
             :new.spriden_first_name,
             :new.spriden_mi,
             :new.spriden_create_user,
             :new.spriden_create_date
         );
END log_rows_spriden;
/

Insert into SPRIDEN_CHANGES (SPRIDEN_PIDM,SPRIDEN_ID,SPRIDEN_LAST_NAME,SPRIDEN_FIRST_NAME,SPRIDEN_MI,SPRIDEN_CREATE_USER,SPRIDEN_CREATE_DATE) values (1000123,'12121210','Artnettetest','Artnettetest','Q','SCTCVT',to_date('19-FEB-21','DD-MON-RR'));
Insert into GORIROL (GORIROL_PIDM,GORIROL_ROLE) values (1000123,'STUDENT');
Trigger on Table [message #683769 is a reply to message #683768] Fri, 19 February 2021 15:06 Go to previous messageGo to next message
ettentrala
Messages: 6
Registered: August 2008
Location: Maryland
Junior Member
Hello,

It would be greatly appreciated if I could get some assistance on creating a trigger on a table. I have a table called SPRIDEN. When a row is entered, the trigger inserts only the new row(S) in the SPRIDEN_CHANGES table. This is working fine. However, I have another table that is GORIROL that I would like to include the GORIROL_ROLE to the SPRIDEN_CHANGES table. Is this possible?

This works....

CREATE OR REPLACE TRIGGER log_rows_spriden
  AFTER INSERT ON SATURN.spriden
  FOR EACH ROW
BEGIN
  INSERT INTO SATURN.spriden_changes
            VALUES
         (
             :new.spriden_pidm,
             :new.spriden_id,
             :new.spriden_last_name,
             :new.spriden_first_name,
             :new.spriden_mi,
             :new.spriden_create_user,
             :new.spriden_create_date
         );
END log_rows_spriden;
/

I tried to add the other table and it doesn't work. What am I doing wrong? This is my first time writing a trigger.

CREATE OR REPLACE TRIGGER log_rows_spriden
  AFTER INSERT ON 
   (SATURN.spriden  JOIN GENERAL.GORIROL
      ON
	  SPRIDEN_PIDM = GORIROL_PIDM
	  WHERE GORIROL_ROLE IN ('STUDENT','ADMITTED','APPLICANT'))
  FOR EACH ROW
BEGIN
  INSERT INTO SATURN.spriden_changes
            VALUES
         (
             :new.spriden_pidm,
             :new.spriden_id,
             :new.spriden_last_name,
             :new.spriden_first_name,
             :new.spriden_mi,
             :new.spriden_create_user,
             :new.spriden_create_date
         );
END log_rows_spriden;
/

Insert into SPRIDEN_CHANGES (SPRIDEN_PIDM,SPRIDEN_ID,SPRIDEN_LAST_NAME,SPRIDEN_FIRST_NAME,SPRIDEN_MI,SPRIDEN_CREATE_USER,SPRIDEN_CREATE_DATE) values (1000123,'12121210','Artnettetest','Artnettetest','Q','SCTCVT',to_date('19-FEB-21','DD-MON-RR'));
Insert into GORIROL (GORIROL_PIDM,GORIROL_ROLE) values (1000123,'STUDENT');
Trigger on Table [message #683770 is a reply to message #683768] Fri, 19 February 2021 15:07 Go to previous messageGo to next message
ettentrala
Messages: 6
Registered: August 2008
Location: Maryland
Junior Member
Hello,

It would be greatly appreciated if I could get some assistance on creating a trigger on a table. I have a table called SPRIDEN. When a row is entered, the trigger inserts only the new row(S) in the SPRIDEN_CHANGES table. This is working fine. However, I have another table that is GORIROL that I would like to include the GORIROL_ROLE to the SPRIDEN_CHANGES table. Is this possible?

This works....

CREATE OR REPLACE TRIGGER log_rows_spriden
  AFTER INSERT ON SATURN.spriden
  FOR EACH ROW
BEGIN
  INSERT INTO SATURN.spriden_changes
            VALUES
         (
             :new.spriden_pidm,
             :new.spriden_id,
             :new.spriden_last_name,
             :new.spriden_first_name,
             :new.spriden_mi,
             :new.spriden_create_user,
             :new.spriden_create_date
         );
END log_rows_spriden;
/

I tried to add the other table and it doesn't work. What am I doing wrong? This is my first time writing a trigger.

CREATE OR REPLACE TRIGGER log_rows_spriden
  AFTER INSERT ON 
   (SATURN.spriden  JOIN GENERAL.GORIROL
      ON
	  SPRIDEN_PIDM = GORIROL_PIDM
	  WHERE GORIROL_ROLE IN ('STUDENT','ADMITTED','APPLICANT'))
  FOR EACH ROW
BEGIN
  INSERT INTO SATURN.spriden_changes
            VALUES
         (
             :new.spriden_pidm,
             :new.spriden_id,
             :new.spriden_last_name,
             :new.spriden_first_name,
             :new.spriden_mi,
             :new.spriden_create_user,
             :new.spriden_create_date
         );
END log_rows_spriden;
/

Insert into SPRIDEN_CHANGES (SPRIDEN_PIDM,SPRIDEN_ID,SPRIDEN_LAST_NAME,SPRIDEN_FIRST_NAME,SPRIDEN_MI,SPRIDEN_CREATE_USER,SPRIDEN_CREATE_DATE) values (1000123,'12121210','Artnettetest','Artnettetest','Q','SCTCVT',to_date('19-FEB-21','DD-MON-RR'));
Insert into GORIROL (GORIROL_PIDM,GORIROL_ROLE) values (1000123,'STUDENT');
Trigger on Table [message #683771 is a reply to message #683768] Fri, 19 February 2021 15:07 Go to previous messageGo to next message
ettentrala
Messages: 6
Registered: August 2008
Location: Maryland
Junior Member
Hello,

It would be greatly appreciated if I could get some assistance on creating a trigger on a table. I have a table called SPRIDEN. When a row is entered, the trigger inserts only the new row(S) in the SPRIDEN_CHANGES table. This is working fine. However, I have another table that is GORIROL that I would like to include the GORIROL_ROLE to the SPRIDEN_CHANGES table. Is this possible?

This works....

CREATE OR REPLACE TRIGGER log_rows_spriden
  AFTER INSERT ON SATURN.spriden
  FOR EACH ROW
BEGIN
  INSERT INTO SATURN.spriden_changes
            VALUES
         (
             :new.spriden_pidm,
             :new.spriden_id,
             :new.spriden_last_name,
             :new.spriden_first_name,
             :new.spriden_mi,
             :new.spriden_create_user,
             :new.spriden_create_date
         );
END log_rows_spriden;
/

I tried to add the other table and it doesn't work. What am I doing wrong? This is my first time writing a trigger.

CREATE OR REPLACE TRIGGER log_rows_spriden
  AFTER INSERT ON 
   (SATURN.spriden  JOIN GENERAL.GORIROL
      ON
	  SPRIDEN_PIDM = GORIROL_PIDM
	  WHERE GORIROL_ROLE IN ('STUDENT','ADMITTED','APPLICANT'))
  FOR EACH ROW
BEGIN
  INSERT INTO SATURN.spriden_changes
            VALUES
         (
             :new.spriden_pidm,
             :new.spriden_id,
             :new.spriden_last_name,
             :new.spriden_first_name,
             :new.spriden_mi,
             :new.spriden_create_user,
             :new.spriden_create_date
         );
END log_rows_spriden;
/

Insert into SPRIDEN_CHANGES (SPRIDEN_PIDM,SPRIDEN_ID,SPRIDEN_LAST_NAME,SPRIDEN_FIRST_NAME,SPRIDEN_MI,SPRIDEN_CREATE_USER,SPRIDEN_CREATE_DATE) values (1000123,'12121210','Artnettetest','Artnettetest','Q','SCTCVT',to_date('19-FEB-21','DD-MON-RR'));
Insert into GORIROL (GORIROL_PIDM,GORIROL_ROLE) values (1000123,'STUDENT');
Re: Trigger on Table (4 merged) [message #683773 is a reply to message #683768] Sat, 20 February 2021 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ This is not clear
2/ Post the CREATE TABLE statements for your tables
3/ Post an example of what you want: what should be the content of your tables after the execution of the INSERT statements.

Note: If you use a specific language in TO_DATE function, you must specify it:
SQL> select to_date('19-FEB-21','DD-MON-RR') from dual;
select to_date('19-FEB-21','DD-MON-RR') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL> select to_date('19-FEB-21','DD-MON-RR','NLS_DATE_LANGUAGE=AMERICAN') from dual;
TO_DATE('19-FEB-21'
-------------------
19/02/2021 00:00:00

1 row selected.
In addition, do not use the RR format, ALWAYS use years with 4 digits.



Re: Trigger on Table [message #683774 is a reply to message #683769] Sat, 20 February 2021 01:28 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
THis structure is not correct:
CREATE OR REPLACE TRIGGER log_rows_spriden
  AFTER INSERT ON 
   (SATURN.spriden  JOIN GENERAL.GORIROL
      ON
	  SPRIDEN_PIDM = GORIROL_PIDM
	  WHERE GORIROL_ROLE IN ('STUDENT','ADMITTED','APPLICANT'))
It looks as though you are trying to create a trigger on an inline view. You can't do that. You could create the view that joins spriden to gorirol and then create an INSTEAD OF trigger on the view. Would that achieve whatever it is you are trying to do?
Previous Topic: postgres Range Type equivalent in oracle.
Next Topic: Assigning one nested table to other in same position
Goto Forum:
  


Current Time: Fri Mar 29 00:33:21 CDT 2024