Solved

DDL and DML triggers

Posted on 2002-03-26
2
1,274 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
Comment Utility
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 34

Accepted Solution

by:
Mark Geerlings earned 30 total points
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now