Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1336
  • Last Modified:

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
0
mundial
Asked:
mundial
1 Solution
 
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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now