Penny Penny - 7 months ago 13
SQL Question

Mysql trigger or stored procedure to update column A on update of column B in same table

I have created a library database and one of the table is

transaction
which is as follows

CREATE TABLE action
(
t_id int NOT NULL AUTO_INCREMENT,
b_id int,
m_id int,
borrow_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
is_returned Bool Default 0,
is_lost Bool,
is_damaged Bool,
return_timestamp TIMESTAMP,
f_id int,
PRIMARY KEY (t_id),
CONSTRAINT fk_bookId FOREIGN KEY (b_id) REFERENCES book(b_id),
CONSTRAINT fk_memberId FOREIGN KEY (m_id) REFERENCES member(m_id),
CONSTRAINT fk_fineId FOREIGN KEY (f_id) REFERENCES fine(f_id)
);


I want to update
return_timestamp
arrtibute to the timestamp which is when
is_returned
is changed to
1


I tried this

CREATE TRIGGER update_return_timestamp
AFTER UPDATE
ON action FOR EACH ROW

BEGIN
DECLARE return_timestamp timestamp;

SELECT is_returned FROM action
IF is_return IS 1
BEGIN
INSERT INTO transaction (return_timestamp) VALUES (now())
END
COMMIT TRANSACTION
END


Where am I going wrong? Or what is the correct approach? I don't know how to do this

Answer

You can declare your Trigger before updating the record. if the new update set is_returned field then you set return_timestamp field.

CREATE TRIGGER update_return_timestamp

BEFORE UPDATE ON `action` FOR EACH ROW
BEGIN

IF NEW.is_returned = 1 Then
    Set NEW.return_timestamp = CURRENT_TIMESTAMP
END IF;

END
Comments