StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I agree with Dave that the desired result probalby can and should be achieved using built-in constraints such as unique indexes (or unique constraints).</p> <p>If you really need to get around the mutating table error, the usual way to do it is to create a package which contains a package-scoped variable that is a table of something that can be used to identify the changed rows (I think ROWID is possible, otherwise you have to use the PK, I don't use Oracle currently so I can't test it). The FOR EACH ROW trigger then fills in this variable with all rows that are modified by the statement, and then there is an AFTER each statement trigger that reads the rows and validate them.</p> <p>Something like (syntax is probably wrong, I haven't worked with Oracle for a few years)</p> <pre><code>CREATE OR REPLACE PACKAGE trigger_pkg; PROCEDURE before_stmt_trigger; PROCEDURE for_each_row_trigger(row IN ROWID); PROCEDURE after_stmt_trigger; END trigger_pkg; CREATE OR REPLACE PACKAGE BODY trigger_pkg AS TYPE rowid_tbl IS TABLE OF(ROWID); modified_rows rowid_tbl; PROCEDURE before_stmt_trigger IS BEGIN modified_rows := rowid_tbl(); END before_each_stmt_trigger; PROCEDURE for_each_row_trigger(row IN ROWID) IS BEGIN modified_rows(modified_rows.COUNT) = row; END for_each_row_trigger; PROCEDURE after_stmt_trigger IS BEGIN FOR i IN 1 .. modified_rows.COUNT LOOP SELECT ... INTO ... FROM the_table WHERE rowid = modified_rows(i); -- do whatever you want to END LOOP; END after_each_stmt_trigger; END trigger_pkg; CREATE OR REPLACE TRIGGER before_stmt_trigger BEFORE INSERT OR UPDATE ON mytable AS BEGIN trigger_pkg.before_stmt_trigger; END; CREATE OR REPLACE TRIGGER after_stmt_trigger AFTER INSERT OR UPDATE ON mytable AS BEGIN trigger_pkg.after_stmt_trigger; END; CREATE OR REPLACE TRIGGER for_each_row_trigger BEFORE INSERT OR UPDATE ON mytable WHEN (new.mycolumn IS NOT NULL) AS BEGIN trigger_pkg.for_each_row_trigger(:new.rowid); END; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload