How to compare and update an inserted value to existing value using a trigger?

Question

I am learning triggers for the first time in SQL, and I am unsure of how to write/phrase a certain trigger.

I am attempting to make a trigger that would check to see if a value within an attempted row insert is greater than the existing value within the table, and then update the value if the new value being added to the table is greater than the current version of that value. So if a user were to try to insert a row containing an attribute with a value of 3 in table Z that contains that same attribute but with a value of 1, the values would be compared then table Z would be updated to have that attribute now have a value of 3.

Triggers are **highly vendor-specific** - so please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Nov 14 '19 at 04:34
Incidentally, in a real world scenario and following a "don't use triggers" mantra, a MERGE statement could be used for this as it's design intent is to "update existing or insert if not exist" — Caius Jard, Nov 14 '19 at 05:18
ps; this post seemed to have suffered a bizarre edit in revision 5; if the intent is to remove it, there is a delete button — Caius Jard, Nov 14 '19 at 05:22

Caius Jard · Accepted Answer · 2019-11-14T05:31:10.587

Re your (now edited out) question about :old and :new - old and new are most applicable to update queries where they allow you to examine the before-update and after-update value of the same row - the scenario you're discussing is one of editing a different row using an insert so :old doesn't really apply - the insert trigger will only have a :new pseudorow. There wouldn't be an :old that refers to a different row existing in the table

Two ways you could do this with a "before insert" trigger - either:

find the existing row and if it exists, copy its data into the :new row then delete the existing row. This is an errorless way of handling the issue but is perhaps longer to code
issue an update query to update an existing row and if a row is updated, raise an application error to prevent the insert of the new row

For a discussion on before insert triggers that prevent inserts in some scenarios see Prevent Insert Trigger - and expect some said "don't use triggers" chatter; it's good advice. Every time I've used triggers a part of me wishes I hadn't :)

ps; I specifically haven't written the code for this for you because you've said it's a learning exercise, but if you feel you'll learn more by reading code from someone else rather than writing it out yourself, let me know and I'll provide some example. Tejash has made a reasonable start on the update route i describe, it just remains to use the sql%rowcount to know how many rows were updated and raise an exception to prevent the insert

score 1 · Answer 2 · answered Nov 14 '19 at 05:10

I am not sure if I understood you correctly but this is what my understanding is from your question.

You have one table: Z (ATTRB, VAL)
You want to create an insert trigger on it.
The trigger should update the VAL of already existing record in case ATTRB of the existing record is same as ATTRB of newly inserted record and VAL of the existing record is lower than newly inserted record's VAL
The trigger should update the value of VAL of the existing record to the VAL of the newly inserted record.

Here is how you can do it using a trigger.

- creating the table and adding one record:

SQL> CREATE TABLE Z (ATTRB VARCHAR2(1), VAL NUMBER);

Table created.

SQL> INSERT INTO Z VALUES ('A',1);

1 row created.

SQL> SELECT * FROM Z;

A        VAL
- ----------
A          1

- Trigger code

SQL> CREATE TRIGGER Z_TRG BEFORE
  2      INSERT ON Z
  3      FOR EACH ROW
  4  BEGIN
  5      UPDATE Z
  6      SET VAL = :NEW.VAL
  7      WHERE ATTRB = :NEW.ATTRB
  8        AND VAL < :NEW.VAL;
  9  END Z_TRG;
 10  /

Trigger created.

- Checking if the trigger is working by inserting other value

SQL> INSERT INTO Z VALUES ('A',2); -- adding higher value of VAL should update the existing record

1 row created.

SQL> SELECT * FROM Z;

A        VAL
- ----------
A          2
A          2

SQL> INSERT INTO Z VALUES ('A',1); -- adding lower value of VAL will do nothing

1 row created.

SQL> SELECT * FROM Z;

A        VAL
- ----------
A          2
A          2
A          1

SQL>

Cheers!!

How to compare and update an inserted value to existing value using a trigger?

2 Answers2

- creating the table and adding one record:

- Trigger code

- Checking if the trigger is working by inserting other value