trigger to update tables from other 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!

elpico.

 

[SantaMufasa]

ElPico,

Since most readers of this forum also read the 9i forum, it's probably less confusing if you post inquiries to one or the other (instead of both) of these forums. I've created a reply on your 9i copy of this thread, so, to keep order on replies, may I suggest we use your 9i post for this particular inquiry? Thanks,

Dave