lundi 5 octobre 2015

Sqlite trigger is not working

I'm trying to achieve automatic timestamps updating, when status of some devices is changed. I have a table like this for devices:

CREATE TABLE devices(
  id int PRIMARY KEY,
  status text,
  "timestamp" datetime,
  FOREIGN KEY (timestamp) REFERENCES timestamps (controls)
);

This is table for timestamps:

CREATE TABLE timestamps (
  "database" datetime NOT NULL DEFAULT(CURRENT_TIMESTAMP),
  controls datetime NOT NULL DEFAULT(CURRENT_TIMESTAMP)
);

When I try this trigger, and modify status column for rows in devices table, I don't see anything changes in timestamps table (particularly it is blank as it was).

CREATE TRIGGER update_timestamps UPDATE OF status ON devices 
BEGIN
  UPDATE timestamps SET controls=datetime('now');
END;

Could you please have a look what is wrong with it?

Aucun commentaire:

Enregistrer un commentaire