Home » SQL & PL/SQL » SQL & PL/SQL » Trigger to update table in other schema (9i)  () 1 Vote
Trigger to update table in other schema [message #408159] Mon, 15 June 2009 00:07 Go to next message
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 #408162 is a reply to message #408159] Mon, 15 June 2009 00:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I need to know How we can write the trigger to update the table in other schema.

Use the editor of choice to write the necessary PL/SQL.
Re: Trigger to update table in other schema [message #408165 is a reply to message #408162] Mon, 15 June 2009 00:19 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Sorry I need to rephrase my lines. I have written trigger on single table that update the column but having trouble updating the table on other schema on the same database.

Rgds
SYD
Re: Trigger to update table in other schema [message #408167 is a reply to message #408159] Mon, 15 June 2009 00:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I have written trigger on single table that update the column but having trouble updating the table on other schema on the same database.

You have table & DDL. We don't
You have data & DML. We don't.
You have code. We don't.
You have error message. We don't.

You have ALL the information & can't debug the problem.
Why do you expect others, who have NO details, to solve your mystery?
Re: Trigger to update table in other schema [message #408170 is a reply to message #408167] Mon, 15 June 2009 00:34 Go to previous messageGo to next message
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 #408171 is a reply to message #408159] Mon, 15 June 2009 00:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>This works but I want to update the column MODI_A in table B on schema B
Congratulations! proceed to do so.
Re: Trigger to update table in other schema [message #408176 is a reply to message #408170] Mon, 15 June 2009 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use UPDATE.

Regards
Michel
Re: Trigger to update table in other schema [message #408233 is a reply to message #408170] Mon, 15 June 2009 07:07 Go to previous messageGo to next message
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 #408307 is a reply to message #408233] Mon, 15 June 2009 12:33 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
And just to add, you'll probably want to put a condition on the update or you'll be updating way too many rows for each update in schema.A
Re: Trigger to update table in other schema [message #408343 is a reply to message #408307] Mon, 15 June 2009 19:06 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi JRowbottom and Joy,

Thanks for your help. I am adding the condition and changing the trigger as JRowbottom suggested and will update you.
Rgds
SYD
Re: Trigger to update table in other schema [message #408352 is a reply to message #408343] Mon, 15 June 2009 20:37 Go to previous messageGo to next message
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 #408354 is a reply to message #408159] Mon, 15 June 2009 20:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>The above is working but this is updating all the rows in schema A table.
because no WHERE clause used.

>The real issue I am having now is how the delete works
What DELETE? I do not see any delete.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Trigger to update table in other schema [message #408356 is a reply to message #408354] Mon, 15 June 2009 20:54 Go to previous messageGo to next message
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 #408358 is a reply to message #408159] Mon, 15 June 2009 21:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I mean the if someone deletes row on one schema how I can update the coloumn on other schema table.
Now I understand.

If a row is DELETED, what should occur to table in other schema?
Re: Trigger to update table in other schema [message #408359 is a reply to message #408358] Mon, 15 June 2009 21:12 Go to previous messageGo to next message
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 #408360 is a reply to message #408159] Mon, 15 June 2009 21:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>If row is deleted in A.table I want to make the column "Modified" in B.Table =sysdate

Is this the same action when INSERT & UPDATE occurs?
Re: Trigger to update table in other schema [message #408362 is a reply to message #408360] Mon, 15 June 2009 21:20 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Yes very true but I am having trouble in where clause Sad
Re: Trigger to update table in other schema [message #408365 is a reply to message #408159] Mon, 15 June 2009 21:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
near the top & about 1/3 from the right is "Search" - click it

Use the following keywords

trigger insert update delete

Search only this sub-forum

Review other posts to see how folks have solved similar issues.
Re: Trigger to update table in other schema [message #408376 is a reply to message #408365] Tue, 16 June 2009 00:35 Go to previous messageGo to next message
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 #408377 is a reply to message #408376] Tue, 16 June 2009 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
:OLD and :NEW variable only exist on row level trigger not on statement level one.
Either add "for each row" or don't use this variable.

Regards
Michel
Re: Trigger to update table in other schema [message #408421 is a reply to message #408377] Tue, 16 June 2009 03:12 Go to previous messageGo to next message
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 #408422 is a reply to message #408421] Tue, 16 June 2009 03:13 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
sorry to mentioned about the error

Line # = 3 Column # = 66 Error Text = PLS-00049: bad bind variable 'NEW.USER_NAME'
Line # = 5 Column # = 66 Error Text = PLS-00049: bad bind variable 'OLD.USER_NAME'

Re: Trigger to update table in other schema [message #408425 is a reply to message #408159] Tue, 16 June 2009 03:19 Go to previous messageGo to next message
cookiemonster
Messages: 13952
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then presumably table xyz doesn't have a column called user_name.
Re: Trigger to update table in other schema [message #408439 is a reply to message #408421] Tue, 16 June 2009 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use SQL*Plus, copy and paste you session and a description of your tables.

Regards
Michel
Re: Trigger to update table in other schema [message #408561 is a reply to message #408439] Tue, 16 June 2009 19:55 Go to previous messageGo to next message
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 #408564 is a reply to message #408159] Tue, 16 June 2009 20:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
USERNAME <> USER_NAME

How do you ensure that a record will exist within XYZ so that UPDATE can succeed?

What needs to be done when no record for USERNAME exists within XYZ?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

[Updated on: Tue, 16 June 2009 20:51]

Report message to a moderator

Re: Trigger to update table in other schema [message #408798 is a reply to message #408564] Wed, 17 June 2009 19:51 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #408801 is a reply to message #408159] Wed, 17 June 2009 20:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>4/38 PL/SQL: ORA-00904: "bbb"."USER_NAME": invalid identifier
Neither Oracle nor I know what BBB.USER_NAME is.
Please clarify or correct.

Re: Trigger to update table in other schema [message #408802 is a reply to message #408801] Wed, 17 June 2009 20:24 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member

sorry bbb should be "users table" I typed wrong

"bbb" is schema and user_name is field of user table and "aaa" is other schema and "options" is table and user_name is field.
Re: Trigger to update table in other schema [message #408803 is a reply to message #408159] Wed, 17 June 2009 20:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Congratulations. No more problems. Enjoy the trigger.
Re: Trigger to update table in other schema [message #408804 is a reply to message #408803] Wed, 17 June 2009 20:28 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
But still getting the same error BalckSwan.
Re: Trigger to update table in other schema [message #408805 is a reply to message #408159] Wed, 17 June 2009 20:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>"bbb" is schema
Where did this schema come from?
Previously you had users "aaa" & "zzz"
What objects does bbb own?

> user_name is field of user table
What/which "user table"?

>But still getting the same error BalckSwan.
The fix the code so Oracle understands what to do.
ERROR? What Error? I don't see any error.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Trigger to update table in other schema [message #408806 is a reply to message #408159] Wed, 17 June 2009 20:47 Go to previous messageGo to next message
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 #408807 is a reply to message #408159] Wed, 17 June 2009 20:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
  1  select keyword from v$RESERVED_WORDS where keyword like 'U%'
  2* order by 1
SQL> /

KEYWORD
------------------------------
U
UB2
UBA
UID
UNARCHIVED
UNBOUND
UNBOUNDED
UNDER
UNDO
UNDROP
UNIFORM
UNION
UNIQUE
UNLIMITED
UNLOCK
UNNEST
UNPACKED
UNPROTECTED
UNQUIESCE
UNRECOVERABLE
UNTIL
UNUSABLE
UNUSED
UPDATABLE
UPDATE
UPDATED
UPD_INDEXES
UPD_JOININDEX
UPGRADE
UPSERT
UROWID
USAGE
USE
USER
USERS
USER_DEFINED
USER_RECYCLEBIN
USE_ANTI
USE_CONCAT
USE_HASH
USE_HASH_AGGREGATION
USE_MERGE
USE_MERGE_CARTESIAN
USE_NL
USE_NL_WITH_INDEX
USE_PRIVATE_OUTLINES
USE_SEMI
USE_STORED_OUTLINES
USE_TTT_FOR_GSETS
USE_WEAK_NAME_RESL
USING

51 rows selected.


It is bad & dangerous to use KEYWORD/Reserved Words for object name, like USERS

Oracle & you might be less confused if you fully qualified table name with owner/schema as in AAA.OPTIONS.USERNAME
Re: Trigger to update table in other schema [message #408814 is a reply to message #408807] Wed, 17 June 2009 23:09 Go to previous messageGo to next message
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 #408815 is a reply to message #408814] Wed, 17 June 2009 23:14 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Quote:
7 where aaa.OPTIONS.USERNAME = bbb.USERS.USER_NAME;
You should use :NEW.USERNAME instead aaa.OPTIONS.USERNAME

By
Vamsi

[Updated on: Wed, 17 June 2009 23:15]

Report message to a moderator

Re: Trigger to update table in other schema [message #408830 is a reply to message #408815] Thu, 18 June 2009 00:11 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
vamsi kasina wrote on Thu, 18 June 2009 06:14
Quote:
7 where aaa.OPTIONS.USERNAME = bbb.USERS.USER_NAME;
You should use :NEW.USERNAME instead aaa.OPTIONS.USERNAME

By
Vamsi

To be precise: you should have used :old.username, since this is a before delete trigger Wink
Re: Trigger to update table in other schema [message #408839 is a reply to message #408830] Thu, 18 June 2009 00:44 Go to previous messageGo to next message
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
Re: Trigger to update table in other schema [message #408841 is a reply to message #408839] Thu, 18 June 2009 00:54 Go to previous messageGo to previous message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi Vamsi,

what I simply want to acheive is if user is deleted in aaa.OPTIONS table then Last_Modified field in bbb.USERS should be updated with sysdate.
Hoping this will explain more.
Thanks
SYD
Previous Topic: Function Parameters throwing ORA-28576: lost RPC connection to external procedure agent (merged)
Next Topic: Query using REGEXP funtion
Goto Forum:
  


Current Time: Sun Nov 10 06:53:47 CST 2024