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 |
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 |
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 |
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 |
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 |
|
Michel Cadot
Messages: 68665 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 |
John Watson
Messages: 8938 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?
|
|
|
Goto Forum:
Current Time: Sun Jun 30 23:30:58 CDT 2024
|