Trigger to update table in other schema [message #408159] |
Mon, 15 June 2009 00:07 |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
Hi Guru's
I need to know How we can write the trigger to update the table in other schema.
like
when any update happens on Table A in schema A, there is a column "XYZ" in TABLE B in schema B need to be update.
Could you guys please help me.
Rgds
SYD
|
|
|
|
|
|
Re: Trigger to update table in other schema [message #408170 is a reply to message #408167] |
Mon, 15 June 2009 00:34 |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
You are 100% right blackSwan !!
hoping this will help.
create or replace trigger For_A
before insert or update on schema.A
for each row
begin
:NEW.MODI_A := sysdate;
end Modi_A_For_A;
This works but I want to update the column MODI_A in table B on schema B
Hoping this is bit clear.
Rgds
SYD
|
|
|
|
|
Re: Trigger to update table in other schema [message #408233 is a reply to message #408170] |
Mon, 15 June 2009 07:07 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You will need to include an explicit UPDAET statement - the :NEW and :OLD syntax only references the single row which has caused this trigger to fire.
SO, it'll be something like:create or replace trigger For_A
before insert or update on schema.A
for each row
begin
:NEW.MODI_A := sysdate;
UPDATE B.TABLE
SET column = value;
end Modi_A_For_A;
You will need to ensure that the user that creates this trigger has an explicit permission to update that table - ie a privilege acquired via a role will not suffice.
|
|
|
|
|
Re: Trigger to update table in other schema [message #408352 is a reply to message #408343] |
Mon, 15 June 2009 20:37 |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
Hi,
create or replace trigger For_A
before insert or update on A.table
for each row
begin
:NEW.MODI_A := sysdate;
UPDATE B.TABLE
SET MODIFIELD = sysdate;
end For_A;
The above is working but this is updating all the rows in schema A table.
I have accountid common in both the tables.
The real issue I am having now is how the delete works
if the account of user is deleted then how I update the table in schema A ??
Please advice.
Rgds
SYD
|
|
|
|
Re: Trigger to update table in other schema [message #408356 is a reply to message #408354] |
Mon, 15 June 2009 20:54 |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
Thanks BlackSwan,
I was looking the link how to post as I know there is trick to put the code. So thanks for putting me in right direction.
>The real issue I am having now is how the delete works
What DELETE? I do not see any delete.
I mean the if someone deletes row on one schema how I can update the coloumn on other schema table.
Rgds
SYD
|
|
|
|
Re: Trigger to update table in other schema [message #408359 is a reply to message #408358] |
Mon, 15 June 2009 21:12 |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
Hi BlaclSwan,
If a row is DELETED, what should occur to table in other schema?
If row is deleted in A.table I want to make the column "Modified" in B.Table =sysdate. and I can use accountid in where clause as both the tables have accountid field common.
Rgds
SYD
|
|
|
|
|
|
Re: Trigger to update table in other schema [message #408376 is a reply to message #408365] |
Tue, 16 June 2009 00:35 |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
Hi BlackSwan,
I have wrote the delete trigger
create or replace trigger For_schemaA
after delete on abc.emp
BEGIN
-- Delete emp from abc schema
delete from abc.emp
where abc.empname = :old.name;
-- Update emp if this changes
update xyz.customers
set LAST_MODIFIED = sysdate
where cust_name = :old.name;
END;
but I am getting errors as below
Line # = 4 Column # = 20 Error Text = PLS-00049: bad bind variable 'OLD.NAME'
Line # = 9 Column # = 21 Error Text = PLS-00049: bad bind variable 'OLD.NAME'
Thanks for your help
Rgds
SYD
|
|
|
|
Re: Trigger to update table in other schema [message #408421 is a reply to message #408377] |
Tue, 16 June 2009 03:12 |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
Hi Michel,
Still I am getting the same error I have tried few options. Could you please telll me how to write.
simply I want to achive is that if any row is deleted in schemaA.table then the coloum of date in other schemaB.table is updated.
CREATE OR REPLACE trigger trg_tab1
AFTER insert OR update OR delete ON schemaA.xyz
FOR EACH ROW
BEGIN
IF insertING THEN
insert INTO schemaB.abc (user_name, LAST_MODIFIED) VALUES (:NEW.user_name, SYSDATE);
ELSE
insert INTO schemaB.abc (user_name, LAST_MODIFIED) VALUES (:OLD.user_name, SYSDATE);
END IF;
END;
/
Thanks for your help
SYD
|
|
|
|
|
|
Re: Trigger to update table in other schema [message #408561 is a reply to message #408439] |
Tue, 16 June 2009 19:55 |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
Hi Michel,
here is the infor regardign tables.
SQL> conn aaa/aaa
Connected.
SQL> desc abc
Name Null? Type
USER_ID NOT NULL VARCHAR2(20)
USER_NAME NOT NULL VARCHAR2(20)
LAST_MODIFIED DATE
SQL> conn zzz/zzz
SQL> desc xyz
Name Null? Type
USERNAME NOT NULL VARCHAR2(20)
CUSTOM_NAME VARCHAR2(4000)
LAST_MODIFIED DATE
what I want to achive is if anything deleted in table abc I want to update the field last_modified in xyz table. Both table belongs to different schemas.
Rgds
SYD
|
|
|
|
Re: Trigger to update table in other schema [message #408798 is a reply to message #408564] |
Wed, 17 June 2009 19:51 |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
Hi BlackSwan,
SQL> CREATE OR REPLACE TRIGGER deleteTest
2 before delete on aaa.OPTIONS
3 for each row
4 begin
5 UPDATE bbb.users
6 SET LAST_MODIFIED = sysdate
7 where aaa.OPTIONS.USERNAME = users.USER_NAME
8 end;
9 /
Warning: Trigger created with compilation errors.
SQL> show error
Errors for TRIGGER deleteTest:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PL/SQL: SQL Statement ignored
4/56 PL/SQL: ORA-00933: SQL command not properly ended
5/4 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
SQL>
USERNAME <> USER_NAME
How do you ensure that a record will exist within XYZ so that UPDATE can succeed?
If USERNAME <> USER_NAME then ignore the update.
Thanks for your help and I hope this will explain you the issue I am having now.
Rgds
SYD
|
|
|
Re: Trigger to update table in other schema [message #408799 is a reply to message #408798] |
Wed, 17 June 2009 20:03 |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
sorry please ignore the previous reply.
SQL> CREATE OR REPLACE TRIGGER deleteTest
2 before delete on aaa.OPTIONS
3 for each row
4 begin
5 UPDATE bbb.users
6 SET LAST_MODIFIED = sysdate
7 where aaa.OPTIONS.USERNAME = bbb.USER_NAME;
8 end;
9 /
Warning: Trigger created with compilation errors.
SQL> show errors
Errors for TRIGGER deleteTest:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PL/SQL: SQL Statement ignored
4/38 PL/SQL: ORA-00904: "bbb"."USER_NAME": invalid identifier
SQL>
|
|
|
|
|
|
|
|
Re: Trigger to update table in other schema [message #408806 is a reply to message #408159] |
Wed, 17 June 2009 20:47 |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
HI BlackSwan,
Frist of all thanks for all your time and help.
Please find below all details.
schema bbb
SQL> desc users
Name Null? Type
USER_ID NOT NULL VARCHAR2(20)
USER_NAME NOT NULL VARCHAR2(20)
LAST_MODIFIED DATE
schema aaa
SQL> desc options
Name Null? Type
USERNAME NOT NULL VARCHAR2(20)
CUSTOM_NAME VARCHAR2(4000)
LAST_MODIFIED DATE
SQL> CREATE OR REPLACE TRIGGER deleteTest
2 before delete on aaa.OPTIONS
3 for each row
4 begin
5 UPDATE bbb.users
6 SET LAST_MODIFIED = sysdate
7 where OPTIONS.USERNAME = USERS.USER_NAME;
8 end;
9 /
Warning: Trigger created with compilation errors.
SQL> show errors
Errors for TRIGGER deleteTest:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PL/SQL: SQL Statement ignored
4/7 PL/SQL: ORA-00904: "OPTIONS"."USERNAME": invalid identifier
SQL>
|
|
|
|
Re: Trigger to update table in other schema [message #408814 is a reply to message #408807] |
Wed, 17 June 2009 23:09 |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
I agree and have tried what you have suggested but still getting the error.
SQL> CREATE OR REPLACE TRIGGER deleteTest
2 before delete on aaa.OPTIONS
3 for each row
4 begin
5 UPDATE bbb.users
6 SET LAST_MODIFIED = sysdate
7 where aaa.OPTIONS.USERNAME = bbb.USERS.USER_NAME;
8 end;
9 /
Warning: Trigger created with compilation errors.
SQL> show errors
Errors for TRIGGER deleteTest:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PL/SQL: SQL Statement ignored
4/7 PL/SQL: ORA-00904: "aaa"."OPTIONS"."USERNAME": invalid
identifier
Thanks for your time.
|
|
|
|
|
Re: Trigger to update table in other schema [message #408839 is a reply to message #408830] |
Thu, 18 June 2009 00:44 |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
Thank you very much Vamsi. Much appreciated your help.
The trigger compile with no errors. But when I delete the row in
table aaa.OPTIONS then Last_Modified field in bbb.USERS is not updated.
Please advice.
Rgds
SYD
|
|
|
|