MySQL Trigger Automatic Update

[geeko]

Mysql database එකක එක table එකක column එකක value එක අනුව තව table එකක column එකක value එකක් automatically update වෙන්න හදන්නේ කොහොමද. Trigger එක කලා ඒත් error එකක් එනවා. Syntax error

SQL QUERY FOR TRIGGER
-----------------------------

CREATE TRIGGER update_winner_trigger AFTER UPDATE ON oc_t_item_meta FOR EACH ROW BEGIN -- Get the current timestamp DECLARE current_timestamp INT; SET current_timestamp = UNIX_TIMESTAMP(); -- Update winner based on the specified conditions UPDATE oc_t_auction AS a JOIN oc_t_item AS i ON a.item_id = i.pk_i_id JOIN oc_t_item_meta AS m1 ON i.pk_i_id = m1.fk_i_item_id JOIN oc_t_item_meta AS m2 ON i.pk_i_id = m2.fk_i_item_id SET a.winner = CASE WHEN m1.fk_i_field_id = 16 AND m1.s_value > current_timestamp AND (m2.fk_i_field_id = 15 AND m2.s_value >= a.bidding_price) THEN 1 WHEN m1.fk_i_field_id != 16 AND i.dt_expiration > current_timestamp AND (m2.fk_i_field_id = 15 AND m2.s_value >= a.bidding_price) THEN 1 ELSE 0 END WHERE a.item_id = NEW.fk_i_item_id; END;

Error
-----

SQL query:



CREATE TRIGGER update_winner_trigger AFTER UPDATE ON oc_t_item_meta
FOR EACH ROW
BEGIN
-- Get the current timestamp
DECLARE current_timestamp INT



MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'current_timestamp INT' at line 5

 

[windows_ubuntu]

Bump

 

[OrangeHat]

bump

 

[@media]

https://stackoverflow.com/questions/4125947/what-is-the-data-type-for-unix-timestamp-mysql

meka poddak balanna. wena aulak penne na

 

[dilw]

Bump

 

[MrFrog]

current_timestamp reserve keyword ekakda MySQL wala?
I'm not familiar with MySQl though.

 

[B.Chat]

Up

 

[geeko]

current_timestamp reserve keyword ekakda MySQL wala?
I'm not familiar with MySQl though.

Code eka change kale. meken trigger eka hadanna puluwan error ekak enne na. eth awashya de venne na

DELIMITER $$

CREATE TRIGGER update_winner_trigger AFTER UPDATE ON oc_t_item_meta
FOR EACH ROW
BEGIN
-- Update winner based on the specified conditions
UPDATE oc_t_auction AS a
JOIN oc_t_item AS i ON a.item_id = i.pk_i_id
SET a.winner =
CASE
WHEN (
(SELECT m1.s_value FROM oc_t_item_meta AS m1 WHERE m1.fk_i_item_id = NEW.fk_i_item_id AND m1.fk_i_field_id = 16) > UNIX_TIMESTAMP()
AND
(SELECT m2.s_value FROM oc_t_item_meta AS m2 WHERE m2.fk_i_item_id = NEW.fk_i_item_id AND m2.fk_i_field_id = 15) >= a.bidding_price
) THEN 1
WHEN i.dt_expiration > UNIX_TIMESTAMP() AND (SELECT m2.s_value FROM oc_t_item_meta AS m2 WHERE m2.fk_i_item_id = NEW.fk_i_item_id AND m2.fk_i_field_id = 15) >= a.bidding_price THEN 1
ELSE 0
END
WHERE a.item_id = NEW.fk_i_item_id;
END;
$$

DELIMITER ;

 

[MrFrog]

Code eka change kale. meken trigger eka hadanna puluwan error ekak enne na. eth awashya de venne na

DELIMITER $$

CREATE TRIGGER update_winner_trigger AFTER UPDATE ON oc_t_item_meta
FOR EACH ROW
BEGIN
-- Update winner based on the specified conditions
UPDATE oc_t_auction AS a
JOIN oc_t_item AS i ON a.item_id = i.pk_i_id
SET a.winner =
CASE
WHEN (
(SELECT m1.s_value FROM oc_t_item_meta AS m1 WHERE m1.fk_i_item_id = NEW.fk_i_item_id AND m1.fk_i_field_id = 16) > UNIX_TIMESTAMP()
AND
(SELECT m2.s_value FROM oc_t_item_meta AS m2 WHERE m2.fk_i_item_id = NEW.fk_i_item_id AND m2.fk_i_field_id = 15) >= a.bidding_price
) THEN 1
WHEN i.dt_expiration > UNIX_TIMESTAMP() AND (SELECT m2.s_value FROM oc_t_item_meta AS m2 WHERE m2.fk_i_item_id = NEW.fk_i_item_id AND m2.fk_i_field_id = 15) >= a.bidding_price THEN 1
ELSE 0
END
WHERE a.item_id = NEW.fk_i_item_id;
END;
$$

DELIMITER ;

Not sure machan. Umba CASE statement eka liyapu widiya mata awul. (not saying it's incorrect because I don't know MySQL syntax).

Can you try your previous code with changing the only current_timestamp variable name to say current_timestamp1? Just to check..

 

[geeko]

https://stackoverflow.com/questions/4125947/what-is-the-data-type-for-unix-timestamp-mysql

meka poddak balanna. wena aulak penne na

code eka venas kala. dan trigger karaddi error ekak enne na. eth one karana function eke venne na

DELIMITER $$

CREATE TRIGGER update_winner_trigger AFTER UPDATE ON oc_t_item_meta
FOR EACH ROW
BEGIN
-- Check if m1_value is greater than current timestamp
IF (SELECT m1.s_value FROM oc_t_item_meta AS m1 WHERE m1.fk_i_item_id = NEW.fk_i_item_id AND m1.fk_i_field_id = 16) > UNIX_TIMESTAMP() THEN
-- Update winner based on m2_value and bidding price
IF (SELECT m2.s_value FROM oc_t_item_meta AS m2 WHERE m2.fk_i_item_id = NEW.fk_i_item_id AND m2.fk_i_field_id = 15) >= a.bidding_price THEN
UPDATE oc_t_auction AS a
JOIN oc_t_item AS i ON a.item_id = i.pk_i_id
SET a.winner = 1
WHERE a.item_id = NEW.fk_i_item_id;
END IF;
-- If m1_value is not greater than current timestamp, check i.dt_expiration
ELSEIF (SELECT i.dt_expiration FROM oc_t_item AS i WHERE i.pk_i_id = NEW.fk_i_item_id) > UNIX_TIMESTAMP() THEN
-- Update winner based on m2_value and bidding price
IF (SELECT m2.s_value FROM oc_t_item_meta AS m2 WHERE m2.fk_i_item_id = NEW.fk_i_item_id AND m2.fk_i_field_id = 15) >= a.bidding_price THEN
UPDATE oc_t_auction AS a
JOIN oc_t_item AS i ON a.item_id = i.pk_i_id
SET a.winner = 1
WHERE a.item_id = NEW.fk_i_item_id;
END IF;
ELSE
-- If neither condition is met, set the winner column to 0
UPDATE oc_t_auction AS a
JOIN oc_t_item AS i ON a.item_id = i.pk_i_id
SET a.winner = 0
WHERE a.item_id = NEW.fk_i_item_id;
END IF;
END;
$$

DELIMITER ;

---

Not sure machan. Umba CASE statement eka liyapu widiya mata awul. (not saying it's incorrect because I don't know MySQL syntax).

Can you try your previous code with changing the only current_timestamp variable name to say current_timestamp1? Just to check..

DELIMITER $$

CREATE TRIGGER update_winner_trigger AFTER UPDATE ON oc_t_item_meta
FOR EACH ROW
BEGIN
-- Check if m1_value is greater than current timestamp
IF (SELECT m1.s_value FROM oc_t_item_meta AS m1 WHERE m1.fk_i_item_id = NEW.fk_i_item_id AND m1.fk_i_field_id = 16) > UNIX_TIMESTAMP() THEN
-- Update winner based on m2_value and bidding price
IF (SELECT m2.s_value FROM oc_t_item_meta AS m2 WHERE m2.fk_i_item_id = NEW.fk_i_item_id AND m2.fk_i_field_id = 15) >= a.bidding_price THEN
UPDATE oc_t_auction AS a
JOIN oc_t_item AS i ON a.item_id = i.pk_i_id
SET a.winner = 1
WHERE a.item_id = NEW.fk_i_item_id;
END IF;
-- If m1_value is not greater than current timestamp, check i.dt_expiration
ELSEIF (SELECT i.dt_expiration FROM oc_t_item AS i WHERE i.pk_i_id = NEW.fk_i_item_id) > UNIX_TIMESTAMP() THEN
-- Update winner based on m2_value and bidding price
IF (SELECT m2.s_value FROM oc_t_item_meta AS m2 WHERE m2.fk_i_item_id = NEW.fk_i_item_id AND m2.fk_i_field_id = 15) >= a.bidding_price THEN
UPDATE oc_t_auction AS a
JOIN oc_t_item AS i ON a.item_id = i.pk_i_id
SET a.winner = 1
WHERE a.item_id = NEW.fk_i_item_id;
END IF;
ELSE
-- If neither condition is met, set the winner column to 0
UPDATE oc_t_auction AS a
JOIN oc_t_item AS i ON a.item_id = i.pk_i_id
SET a.winner = 0
WHERE a.item_id = NEW.fk_i_item_id;
END IF;
END;
$$

DELIMITER ;


if else dala venas kala ban. dan error eka enne na eth one karana de venne na
------ Post added on Aug 1, 2023 at 3:56 PM

 

[Schrodinger Cat]

bump

 

[Metal boy]

Bump

 

[Any.key]

bump

 

[EKGuest]

bump

 

[MrFrog]

code eka venas kala. dan trigger karaddi error ekak enne na. eth one karana function eke venne na

DELIMITER $$

CREATE TRIGGER update_winner_trigger AFTER UPDATE ON oc_t_item_meta
FOR EACH ROW
BEGIN
-- Check if m1_value is greater than current timestamp
IF (SELECT m1.s_value FROM oc_t_item_meta AS m1 WHERE m1.fk_i_item_id = NEW.fk_i_item_id AND m1.fk_i_field_id = 16) > UNIX_TIMESTAMP() THEN
-- Update winner based on m2_value and bidding price
IF (SELECT m2.s_value FROM oc_t_item_meta AS m2 WHERE m2.fk_i_item_id = NEW.fk_i_item_id AND m2.fk_i_field_id = 15) >= a.bidding_price THEN
UPDATE oc_t_auction AS a
JOIN oc_t_item AS i ON a.item_id = i.pk_i_id
SET a.winner = 1
WHERE a.item_id = NEW.fk_i_item_id;
END IF;
-- If m1_value is not greater than current timestamp, check i.dt_expiration
ELSEIF (SELECT i.dt_expiration FROM oc_t_item AS i WHERE i.pk_i_id = NEW.fk_i_item_id) > UNIX_TIMESTAMP() THEN
-- Update winner based on m2_value and bidding price
IF (SELECT m2.s_value FROM oc_t_item_meta AS m2 WHERE m2.fk_i_item_id = NEW.fk_i_item_id AND m2.fk_i_field_id = 15) >= a.bidding_price THEN
UPDATE oc_t_auction AS a
JOIN oc_t_item AS i ON a.item_id = i.pk_i_id
SET a.winner = 1
WHERE a.item_id = NEW.fk_i_item_id;
END IF;
ELSE
-- If neither condition is met, set the winner column to 0
UPDATE oc_t_auction AS a
JOIN oc_t_item AS i ON a.item_id = i.pk_i_id
SET a.winner = 0
WHERE a.item_id = NEW.fk_i_item_id;
END IF;
END;
$$

DELIMITER ;

---

DELIMITER $$

CREATE TRIGGER update_winner_trigger AFTER UPDATE ON oc_t_item_meta
FOR EACH ROW
BEGIN
-- Check if m1_value is greater than current timestamp
IF (SELECT m1.s_value FROM oc_t_item_meta AS m1 WHERE m1.fk_i_item_id = NEW.fk_i_item_id AND m1.fk_i_field_id = 16) > UNIX_TIMESTAMP() THEN
-- Update winner based on m2_value and bidding price
IF (SELECT m2.s_value FROM oc_t_item_meta AS m2 WHERE m2.fk_i_item_id = NEW.fk_i_item_id AND m2.fk_i_field_id = 15) >= a.bidding_price THEN
UPDATE oc_t_auction AS a
JOIN oc_t_item AS i ON a.item_id = i.pk_i_id
SET a.winner = 1
WHERE a.item_id = NEW.fk_i_item_id;
END IF;
-- If m1_value is not greater than current timestamp, check i.dt_expiration
ELSEIF (SELECT i.dt_expiration FROM oc_t_item AS i WHERE i.pk_i_id = NEW.fk_i_item_id) > UNIX_TIMESTAMP() THEN
-- Update winner based on m2_value and bidding price
IF (SELECT m2.s_value FROM oc_t_item_meta AS m2 WHERE m2.fk_i_item_id = NEW.fk_i_item_id AND m2.fk_i_field_id = 15) >= a.bidding_price THEN
UPDATE oc_t_auction AS a
JOIN oc_t_item AS i ON a.item_id = i.pk_i_id
SET a.winner = 1
WHERE a.item_id = NEW.fk_i_item_id;
END IF;
ELSE
-- If neither condition is met, set the winner column to 0
UPDATE oc_t_auction AS a
JOIN oc_t_item AS i ON a.item_id = i.pk_i_id
SET a.winner = 0
WHERE a.item_id = NEW.fk_i_item_id;
END IF;
END;
$$

DELIMITER ;


if else dala venas kala ban. dan error eka enne na eth one karana de venne na
------ Post added on Aug 1, 2023 at 3:56 PM

If there is no error, and still it doesn't work, most probably your query inside the trigger might not return any results. You need to carefully check those conditions are satisfied . You used the same table oc_t_item_meta with two aliases m1 and m2. I can see in one condition you compare its s_value with UNIX_TIMESTAMP() while in the next condition you compare s_value with bidding_price. May be I'm missing something here, but it is hard to troubleshoot without the knowledge of the context and a proper dataset.

 

[Psiko]

up