Does the order of the DML statements matter when using the "of" clause?

Question

I noticed that Oracle SQL Developer (19.1.0.094.2042) cited a syntax error (via highlight) for a database trigger that has been enabled since 2013 (I just started), a trigger that complies without errors and fires. Oracle was just updated from 11g to 18c. Now I'm inquiring whether the order of the DML statements matters when using the "of" clause.

When I change the order of "DELETE" and "UPDATE," it compiles just the same, albeit without the error highlighting.

With syntax error:

AFTER UPDATE OR DELETE OF column_name ON table_name
FOR EACH ROW

Without syntax error:

AFTER DELETE OR UPDATE OF column_name ON table_name
FOR EACH ROW

It seems prima facie odd to use the first ordering since it seems to grammatically imply that both disjuncts apply to the "of" clause. There is no use for "DELETE OF." However, I couldn't find this question, or an explanation, anywhere, and while I saw both orderings, there were actually more instances of "UPDATE OR DELETE OF" than "DELETE OR UPDATE OF."

I'm still new to the IT industry (< 1 year), and this is my first post; I appreciate any feedback.

Answer 1

Yes it matters. The one which doesn't fail is in effect saying

AFTER (DELETE) OR (UPDATE OF COLUMN_NAME) ON TABLE_NAME

The one which fails is (trying) to say

AFTER (UPDATE) OR (DELETE OF COLUMN_NAME) ON TABLE_NAME

(Here the parentheses are only for purposes of clarification - using them in Real Code (tm) would also result in a syntax error).

'OF COLUMN_NAME' is only valid in conjunction with UPDATE. When associated with DELETE it's a syntax error.

If you hadn't specified a column name you could phrase it either way, e.g. as

AFTER UPDATE OR DELETE ON TABLE_NAME

or

AFTER DELETE OR UPDATE ON TABLE_NAME

---

Edit

To quote Popeye, "Well, blow me down!!!".

I'm surprised, but Oracle will actually accept the OF column_name after either UPDATE or DELETE, and apparently it fires correctly in either case, treating it as if the trigger was written for UPDATE OF COLUMN_NAME even if the UPDATE does not immediately precede OF COLUMN_NAME.

dbfiddle here

So apparently the error message is solely being produced by SQL Developer, and the database happily accepts it where and when ever.

I learn somethin' new ever' day. :-)

Answer 2

I see no required order defined in the syntax diagram, so I'm thinking you might have found a bug in sqldeveloper.