I am creating a trigger in which I want to update fields from another table. Please see below trigger which I have written:
create or replace
TRIGGER RE_SE_INSERT
AFTER INSERT OR UPDATE ON USER FOR EACH ROW
BEGIN
update user set
:new.ci_No = (select ci_No from customer where ci_id = :new.ci_id),
:new.ci_name = (select ci_name from customer where ci_id = :new.ci_id),
:new.IS_deleted = (select deleted from customer where ci_id = :new.ci_id);
END;
I have checked for the column name whether they are wrong but all the column name are correct as per the table but still I get ORA-01747 error of invalid table.column.
Could you please help me here?
Thanks in advance! Tapan
create or replace trigger RE_SE_INSERT
BEFORE INSERT OR UPDATE --create a BEFORE insert/update trigger
ON USER FOR EACH ROW
DECLARE
CURSOR new_cur
IS
SELECT ci_no
,ci_name
,deleted
FROM customer
where ci_id = :new.ci_id;
new_row new_cur%ROWTYPE;
BEGIN
--update user set --remove this statement
OPEN new_cur;
FETCH new_cur INTO new_row;
IF new_cur%FOUND THEN
:new.ci_No := new_row.ci_no;
:new.ci_name := new_row.ci_name;
:new.IS_deleted := new_row.deleted;
END IF;
CLOSE new_cur;
END RE_SE_INSERT;
/
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments