I have different table owners.
Table1: Owner DBA Table2: Owner user1 Table2: Owner user2
I have a trigger on the Table1, which has a select
select @something from Table2 where id = @id
Since there is no any Table2 with DBA owner, so I need to specify the table owner. If I specify user1.Table2, in that case when the user2 is logged in, it will still select from user1.Table. So can I get use some condition there so that it selects from the Table2 of the user which is logged in? Thanks.
Rather than use a permanent table for each user, you could use a GLOBAL TEMPORARY TABLE which has a common schema but a separate instance for each and every connection. Then you wouldn't have to qualify the table name in the trigger; you'd simply reference the global temp table in the trigger, and the contents of that table would be specific to the current connection.
If you have to use a permanent table, I don't see an alternative to constructing the statement within the trigger as a string, and then using EXECUTE IMMEDIATE to execute the constructed statement.
answered 22 Feb '13, 09:01