trigger to update multiple schemas!

[elpico]

Greetings all,

I'm trying to create a trigger to update a seperate table from multiple schemas. The trigger will loaded into my schema called 'global'.

the problem I'm getting in my script is that I haven't been able to pass in the schema name of the table I want to update - Isn't this possible somehow???

In the snippet below, I've tried to run through a list of schema names, and append the table name to the end of each select ( USER_TABLE ), but the trigger errors when I update global in any way...


CREATE OR REPLACE TRIGGER global.POP_DETAILS
AFTER INSERT OR UPDATE OR DELETE
ON global.USER_TABLE FOR EACH ROW DECLARE
CURSOR c1 IS
SELECT SCHEMA_NAME || '.USERTABLE'
FROM SLIST
WHERE SCHEMA_NAME <> 'GLOBAL';
my_tname SLIST.SCHEMA_NAME%TYPE;
sql_stmt VARCHAR2(2048);
BEGIN
OPEN c1;

FETCH c1 INTO my_tname;
EXIT WHEN c1%NOTFOUND;

sql_stmt := 'UPDATE '||my_tname||'
SET
USR_NM = :USR_NM,
USR_PWD = :USR_PWD,
WHERE usr_id=:USR_ID';
EXECUTE IMMEDIATE sql_stmt
.....

Any ideas on alternative ways to do this would be greatly appreciated!

 

[SantaMufasa]

ElPico,

I was able to get your code working when I did not use bind variables (example, &quot; :USR_NM &quot. (When I used the bind variables, my executing the triggering event caused my client session to hang.) If ':USR_NM', ':USR_PWD', and ':USR_ID' represent values that reside in 'global.user_table', then I believe we have your problem solved. Let me know on that.

Also, if you could explain your business need a little bit more, then I'd be more comfortable with your logic. For example, if you are doing an 'INSERT' or 'DELETE' on 'global.user_table', will there yet be a corresponding entry in 'OtherSchema.USERTABLE' for USR_ID? ...and what good does it do to update the &quot;OtherSchema.USERTABLE's&quot; USR_NM and USR_PWD if you are performing a DELETE?

Dave