0
votes
0answers
24 views

What is the story about Oracle CREATE TRIGGER … CALL syntax?

I was able to use the CREATE TRIGGER ... CALL SOMEPROCEDURE; syntax but I'm a little bewildered about it. I expected to see some implicit arguments like TABLE%ROWTYPE new but that doesn't seem to be ...
0
votes
0answers
43 views

Issue with a where clause by using Oracle

I'm currently facing a problem while I'm adding a trigger to the database that I use at home. I'm using : Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production The problem ...
0
votes
1answer
63 views

PLS-00049 bad bind variable when compiling a trigger

I'm trying to implement a dedupe trigger to a table. CREATE OR REPLACE TRIGGER USER.TBL_ACTION_DEDUP BEFORE INSERT ON USER.ERRORTABLE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF ...
0
votes
1answer
30 views

Implement a deduplication trigger in Oracle

I am new in Oracle DB. We are using Toad for Oracle. I need to implement a deduplication trigger for our table. It's an alarms table and a third party vendor will be sending alarms to our database. ...
0
votes
2answers
63 views

How to make a trigger work after a certain UPDATE

I would like to know if it's possible to create a trigger on Oracle that is invoked only when a certain UPDATE is done. For example: UPDATE colors SET tastes_good = y WHERE color_name = 'Apple'; ...
0
votes
1answer
49 views

What does this TRIGGER do? [closed]

I have this trigger in DB2 that I need to convert to ORACLE, but I don't know how it works in DB2 so i can translate it to a ORACLE one, I think it doesn't do anything. CREATE TRIGGER ERRLOG_TRIGGER ...
1
vote
0answers
80 views

DDL trigger AFTER ALTER ON SCHEMA behavior

Can anyone please tell me what's going on here: CREATE TRIGGER afr_alt_trig AFTER ALTER ON SCHEMA cols ora_name_list_t; BEGIN IF (ora_dict_obj_type = 'TABLE') THEN select column_name bulk ...
1
vote
2answers
97 views

Audit table insert restriction

So I'm trying to create my own audit table for some user management analysis and stumbled across a little problem. I use this trigger CREATE OR REPLACE TRIGGER usr_t_audit_user_alnd AFTER LOGON ...
0
votes
1answer
89 views

Trigger in trigger or trigger before table creation

I need to create trigger inside trigger as follows: SQL> CREATE OR REPLACE TRIGGER table_account_modify AFTER CREATE ON SCHEMA 2 DECLARE 3 NAZWA_TABELI varchar(250); 4 sq0 VARCHAR2 ...
2
votes
1answer
282 views

Are there tips for writing Oracle DDL Triggers for auditing? Or should I just use AUDIT?

I've been tinkering with writing a trigger to log my table creation and manipulation for later tracking and review. The challenge is that my trigger logic is stored in the same schema on which I've ...
0
votes
1answer
132 views

Error in trigger: table is mutating

CREATE OR REPLACE TRIGGER "TRANSACTION" before update of totalunitsbought on SIPHOLDER referencing new AS NEW old AS OLD for each row begin declare mportfolioname varchar2(20); ...
2
votes
1answer
231 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
374 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
649 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 ...
2
votes
1answer
590 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
1k 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 ...
5
votes
1answer
2k 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 ...
9
votes
1answer
1k 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
1k 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
283 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
400 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
171 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
1k 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
296 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
362 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 ...
5
votes
2answers
3k 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
441 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
199 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.
2
votes
2answers
622 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
1k 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
382 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
952 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? ...