CODEWITHSUNDEEP
mysqltriggers
Fahad.ag
Fahad.ag

Reputation: 181

How to create MySQL Trigger?

Can someone please help me with MySQL Trigger

Lets say I have two Tables

Table_Current
No (INT TYPE)
Name (VARCHAR TYPE)
Value (DECIMAL TYPE)

Table_Record
No (INT TYPE)
Name (VARCHAR TYPE)
Value (DECIMAL TYPE)

So what I want to achieve is that, I perform replace statements on Table.Current, and if the value of field 'Value' is smaller than that of the Value in Table.Record, I would like to update the Value of Table.Record,

Here is what I have as a trigger

DELIMITER $$

CREATE TRIGGER Mytrigger
AFTER INSERT ON Table_Current

FOR EACH ROW
BEGIN

DECLARE CB DECIMAL (6,3);
DECLARE OB DECIMAL (6,3);

OB = SELECT Value FROM Table_Record WHERE Name=NEW.Name;
CB = NEW.Value;
IF CB<OB THEN
UPDATE Table_Record SET Value = NEW.Value WHERE Name = NEW.Name;
END IF;

END $$
DELIMITER;

Upvotes: 2

Views: 3550

Answers (1)

Simon at The Access Group
Simon at The Access Group

Reputation: 7027

You can simplify this massively. What you are trying to do is effectively this query

    UPDATE Table_Record

    SET `Value` = NEW.`Value` 
    WHERE `Name` = NEW.`Name`
    AND `Value` < NEW.`Value`;

You are interested in updating the row in Table_Record where Name = NEW.Name. You only want to update if the NEW.Value is larger than the one currently stored. Therefore the above will not update if that value constraint is not satisfied.

This trigger code should do what you are trying to achieve.

-- First of all, drop any other trigger with the same name
DROP TRIGGER IF EXISTS `Mytrigger`;

-- Create the new trigger
DELIMITER $$

CREATE TRIGGER `Mytrigger`
AFTER INSERT ON Table_Current

FOR EACH ROW
BEGIN

    UPDATE Table_Record

    SET `Value` = NEW.`Value` 
    WHERE `Name` = NEW.`Name`
    AND `Value` < NEW.`Value`;

END $$
DELIMITER;

Upvotes: 2

Related Questions