CODEWITHSUNDEEP
triggerssql-insert
Corey Stauffer
Corey Stauffer

Reputation: 13

SQL Trigger Insert After Modify Value

I'm looking to make a trigger on new insert to modify a value. The scenario is as follows. When a new entry to the table is added, and if the ObjectTypeID is between 684 and 750, update the quality value to 100. From what I have read so far, I believe this should be done on insert after, but I am still new to SQL and learning. I have been unable to get this to work so far.

Here is my current attempt:

CREATE TRIGGER `items_after_insert` AFTER INSERT ON `items` FOR EACH ROW 
 BEGIN
 SET items.Quality = '100'
 FROM items
 WHERE items.ObjectTypeID > '653' AND items.ObjectTypeID < '751'
 END

Solved using the suggestion below:

CREATE TRIGGER `items_before_insert` BEFORE INSERT ON `items` FOR EACH ROW BEGIN
    IF NEW.ObjectTypeID > '653' AND NEW.ObjectTypeID <'751' THEN
        SET NEW.Quality = '100';
    END IF;
END

Upvotes: 1

Views: 61

Answers (1)

Celso Marigo Jr
Celso Marigo Jr

Reputation: 754

You can use the keywords NEW and OLD on triggers to refer to inserted registers and updated registers.

Look at this example from MySQL documentation:

CREATE TRIGGER upd_check BEFORE UPDATE ON account
 FOR EACH ROW
 BEGIN
     IF NEW.amount < 0 THEN
         SET NEW.amount = 0;
     ELSEIF NEW.amount > 100 THEN
         SET NEW.amount = 100;
     END IF;
 END;

Now I think it´s easy to U solve your problem.

Upvotes: 1

Related Questions