Solved

DDL and DML triggers

Posted on 2002-03-26
2
1,300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 35

Accepted Solution

by:
Mark Geerlings earned 30 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

751 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