Tagged Questions

2
votes
1answer
34 views

Testing AFTER INSERT Trigger

I have the following table: CREATE TABLE Train( Train_No integer PRIMARY KEY, Loco_No integer REFERENCES Locomotive(Loco_No), Back_Loco_No float REFERENCES Locomotive(Loco_No), ...
1
vote
0answers
29 views

Why Oracle Database trigger can became DISABLED

We have an application, using ddl database trigger on oracle database. Trigger depends on package and several tables, package depends on user defined function. It works on different customer sites ...
2
votes
4answers
309 views

A database trigger is an alternative for?

I had the following quiz question and could not answer it myself because of a little confusion. Please answer this and tell me why? A database trigger is an alternative for? A. Stored ...
1
vote
1answer
153 views

Is it possible to monitor role and privilege grant/revoke using triggers?

Is it possible to monitor role and privilege grant/revoke using triggers? I' aware of doing that using Oracle audit tools however, it's interesting is it possible to do that using triggers.
2
votes
1answer
437 views

What is the difference between ON SCHEMA and ON DATABASE triggers?

We can create the database trigger on concrete schema event (ON SCOTT.SCHEMA) or on all schemas (ON SCHEMA). However, we can also use ON DATABASE when creating database trigger. What is the difference ...
3
votes
1answer
424 views

Why I don't need to COMMIT in database trigger?

We can't COMMIT/ROLLBACK in DML triggers because transaction is handled manually after DML statement. However, database triggers seems to be an exception. For example, suppose there's a database ...
7
votes
1answer
624 views

Why am I NOT getting a mutating table error in trigger?

It's (or at least was) known that you cannot use DML statements on a mutating table inside a trigger. An excerpt from the Oracle documentation: A mutating table is a table that is being modified ...
2
votes
2answers
616 views

Oracle - abort within a “before insert” trigger without throwing an exception

I know that this is an awful thing to do for many reasons, but I am in Get Things Done mode and we have a piece of software, which we can't modify, that writes records to a table. Some of these ...
2
votes
2answers
185 views

simulate sequence on mysql in preparation for move to oracle

I've been developing an application against MySQL while a new development server has been slowly being set up. The new dev server will use oracle, and since we're getting close to it being ready, I ...
2
votes
1answer
246 views

Skip running trigger during cascade delete?

I have a legacy application where I have two tables X and Y. Table Y has a trigger that performs an action automatically on delete. There is also a foreign key with cascade delete defined between X ...
1
vote
3answers
148 views

A beautiful trigger issue. Flawed logic? Updating Orderheader when orderlines are complete

THe bussiness rule is as such: The orderlines must be updated with a date stamp when all the related orderlines are marked as 'Y' denoting a completed order line. THe trigger thus far is: CREATE OR ...
1
vote
1answer
608 views

bad bind variable in trigger

i'm trying to update the table whenever complaint has been registered create or replace trigger complaint_advertise BEFORE INSERT ON COMPLAINT_INFO FOR EACH ROW DECLARE BEGIN UPDATE ...
1
vote
1answer
191 views

Error with Oracle trigger, invalid identifier

I have the following DML statement that is working perfectly, i have tried to make it into a trigger but it isn't working. DML statement UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL = NVL((SELECT ...
3
votes
1answer
224 views

How to qualify variable inside trigger body (PL/SQL )?

I can explicitly qualify variable in the body of stored procedure or function : create or replace PROCEDURE ptest AS int_val INT; BEGIN ptest.int_val:=0; END; / How to do the same inside trigger ...
4
votes
2answers
2k views

Database logon trigger

To block a particular user and IP combination on an Oracle Database , I created the following trigger, and compiled without errors. Create or replace trigger you_may_not_login after logon on database ...
5
votes
1answer
313 views

If an 'after' DDL trigger causes an error, is the DDL rolled back?

It has been suggested that DDL is logically performed something like this: begin COMMIT; perform any appropriate pre-DDL trigger code; do the ddl; perform any appropriate post-DDL ...
3
votes
1answer
165 views

Accessing ORA_ROWSCN in the triggers body

Is it possible to access ORA_ROWSCN pseudo-column in the body of BEFORE triggers? I'm thinking about storing this value in the table itself to emulate SQLServer rowversion behaviour.
1
vote
2answers
412 views

Is it possible for me to be notified if an 'after insert or update trigger' stops working?

Last week, I found my after insert or update trigger wasn't working. After I disabled and enabled it, it started working again. I do not yet know why it stopped working. Is there any way to deal with ...
1
vote
1answer
763 views

ADMINISTER DATABASE TRIGGER privilege

What does ADMINISTER DATABASE TRIGGER privilege actually mean? I have the following case: I have 2 databases: DB1 and DB2 On DB1 I have a trigger which arises after logon on database and checks if ...
7
votes
1answer
315 views

How can I rewrite for SQL Server a trigger that writing for Oracle?

How can I rewrite for SQL Server a trigger that was originally written for Oracle ? Here is my Oracle trigger code: CREATE OR REPLACE TRIGGER P000KUL_TEST BEFORE INSERT ON P000KUL REFERENCING NEW AS ...
5
votes
3answers
720 views

Alternatives to an INSERT Trigger

Looking for an example code in Oracle to do the same. i.e. Remove an insert trigger on table T1 whose job is to create a row in T2 based on the derived data. How would this PL-SQL api look like? ...