Improve company productivity with a Business Account.Sign Up

x
?
Solved

DDL and DML triggers

Posted on 2002-03-26
2
Medium Priority
?
1,365 Views
Last Modified: 2012-05-04
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
Comment
Question by:mundial
2 Comments
 
LVL 22

Expert Comment

by:DrSQL
ID: 6899716
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
 
LVL 36

Accepted Solution

by:
Mark Geerlings earned 120 total points
ID: 6900562
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

585 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question