CODEWITHSUNDEEP
sqlite
Martin Dusek
Martin Dusek

Reputation: 1292

SQLite trigger after update

My table has timestamp column. I want a trigger which sets timestamp to 0 on affected rows when a row is updated and the timestamp is not specified in the update statement.

If I use this trigger:

CREATE TRIGGER AFTER UPDATE ON mytable FOR EACH ROW
WHEN (NEW.timestamp IS NULL)
BEGIN
UPDATE mytable SET timestamp = 0 WHERE id = NEW.id;
END;

then the trigger doesn't fire for this update statement:

UPDATE mytable SET comecolumn='some'

I.e. timestamp of affected rows doesn't change to 0.

Can you please help me define the trigger?

Upvotes: 1

Views: 9076

Answers (3)

MEdwin
MEdwin

Reputation: 2960

Here is another way:

import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
name = {'name':'jack'}

c.execute("""CREATE TABLE Programs (
    id INTEGER PRIMARY KEY,
    name VARCHAR(64) NOT NULL,
    time_added INTEGER
);""")

c.execute("""CREATE TRIGGER program_time_added AFTER INSERT ON Programs
    FOR EACH ROW
    BEGIN
        UPDATE Programs SET time_added =datetime('now', 'localtime') WHERE id = NEW.id;
    END;""")

c.execute('INSERT INTO Programs (name) VALUES (?)', [name['name']])

Upvotes: 0

CL.
CL.

Reputation: 180270

The only way to make additional changes to a row in an UPDATE trigger is to execute another UPDATE on the same table afterwards.

The only way to detect whether a column value is changed is to compare the old and the new row values; the trigger does not know which columns actually were mentioned in the original UPDATE statement.

To prevent the trigger from triggering itself recursively, you should restrict it to be triggered by changes of all columns except the timestamp:

CREATE TRIGGER clear_timestamp
AFTER UPDATE OF all_the, other, columns ON MyTable
FOR EACH ROW
WHEN OLD.timestamp = NEW.timestamp
BEGIN
    UPDATE MyTable
    SET timestamp = 0
    WHERE id = NEW.id;
END;

Upvotes: 5

LeBlue
LeBlue

Reputation: 635

I think the problem is that in the SET statement is expanded to every column, with every column set to the current value in the database. So the original only trigger works, if the current timestamp column is NULL.

A solution could be to create another trigger that resets the timestamp column to NULL before an UPDATE.

CREATE TRIGGER "set_null"
BEFORE UPDATE ON "mytable" FOR EACH ROW 
BEGIN
UPDATE mytable set timestamp = NULL where rowid = NEW.rowid;
END

This way the NEW.timestamp is NULL if it is not specified in the UPDATE SET.

Obviously now a NOT NULL constraint cannot be set on timestamp.

Another problem is that trigger recursion must be off when executing a update query:

PRAGMA recursive_triggers = OFF;

Upvotes: 0

Related Questions