DDL and DML triggers

I know about DDL triggers at schema level and DML ones at table level. Is the other way round also possible. I am using DDL trigger at schema level to prevent DDL's for any object. Now I want to apply DML on all schemas if applied in one schema. Can I have a trigger in a cursor where the cursor is helping us to loop through all the tables. I also want delete revoked from all except 3 tables. Will there be any conflict from 1st requirement with 2nd.
--- mundial
mundialAsked:
Who is Participating?
 
Mark GeerlingsDatabase AdministratorCommented:
So you want a DML trigger, like an "after update ... for each row" to look for tables in other schemas with the same name and apply the change to those tables as well?  For example, if the SQL statement is:
update emp set sal = sal * 1.1 where empno = 1;
you would like a trigger on this table to make that same update to other "emp" tables in the database in other schemas, if any?

Yes, that should be possible if:
1. all updates happen in one schema only
2. that schema has triggers on each table and no other schemas have DML triggers (or you use a package variable or some other mechanism to prevent an endless loop of triggers)
3. that schema has select, insert, update and delete privileges on all other schemas

I think your question about revoking the delete privilege is a separate issue, right?  Obviously the schema doing the DML replication needs to have delete rights (if you allow deletes at all).
0
 
DrSQLCommented:
mundial,
    And you believe this is a 30 point question?  Just as a friendly piece of advice, the minimum recommendation for simple questions (from EE grading guidelines) is 50.

    You might try adding some examples. It is possible to have higher-level DML triggers, but I really can't follow your question well enough to tell you if that is pertinent.

Good luck!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.