Testing For Primary Key Existence In Oracle SQL Trigger

Question

I'm trying to write a trigger within SQL, and the code within needs to determine whether an entry exists in the table before either attempting to update, or insert.

I have tried using both

IF EXISTS
UPDATE
ELSE
INSERT

And

UPDATE
IF @@ROWCOUNT = 0
INSERT

But neither of them work. I'm partial to using the latter because my employer is nuts about efficiency (well...duh...) For this reason I'm also reluctant to use

IF SELECT COUNT(*) = 0
UPDATE
ELSE
INSERT

Does anybody know any ways to get around this?

--

UPDATE: I am trying to use MERGE, but I am receiving several errors...

MERGE INTO [tableName] AS Target
USING (SELECT :NEW.PIDM) AS Source (PIDM)
ON (Target.PIDM = Source.PIDM)
WHEN MATCHED THEN
    [UPDATE STATEMENT]
WHEN NOT MATCHED THEN
    [INSERT STATEMENT]

This gives me an error complaining that I'm missing the 'USING' keyword, as well as another complaining about the WHEN statement...

Might your application have more than one user connected at the same time? If so, none of these methods will work. You need some way to serialize access to your update (e.g. a unique constraint, or a database lock). — Jeffrey Kemp, Sep 24 '12 at 03:53

score 2 · Accepted Answer · answered Jun 03 '11 at 00:47

2

Use MERGE instead

answered Jun 03 '11 at 00:47

zerkms

249,484
69
436
539

I was going to say that it falls over, but my code is breaking the comment - I will edit the question... – Dwaine Bailey Jun 03 '11 at 01:24

score 1 · Answer 2 · edited May 23 '17 at 12:19

In PL/SQL, you would use SQL%ROWCOUNT instead of @@ROWCOUNT:

UPDATE (...);
IF SQL%ROWCOUNT = 0 THEN
    INSERT (...);
END IF;

Or, you could use SQL%NOTFOUND, which I personally think is easier to understand the intent:

UPDATE (...);
IF SQL%NOTFOUND THEN
    INSERT (...);
END IF;

As for the MERGE command, the Oracle syntax is slightly different from the SQL Server which @zerkms linked to. In particular, you should leave the word "AS" out of the table alias, and you shouldn't list columns after the "USING" clause. Also, in Oracle SQL, the FROM clause is mandatory. You can use the DUAL table to mimic SQL Server's behavior. Putting it all together (this is untested):

MERGE INTO tableName Target
USING (SELECT :NEW.PIDM FROM DUAL) Source
ON (Target.PIDM = Source.PIDM)
WHEN MATCHED THEN
    [UPDATE STATEMENT]
WHEN NOT MATCHED THEN
    [INSERT STATEMENT]

Testing For Primary Key Existence In Oracle SQL Trigger

2 Answers2