Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I update New in before insert trigger in sqlite?

Tags:

sql

sqlite

For example:

create table test (id numeric, t date not null);

create trigger test_in
before insert on test
for each row
when New.t is null
begin
 -- set New.t = now();
end;

Set New.t didn't work, where can be only select/insert/update/delete stmt. I can not change the database structure (can set default value). After insert trigger also not suitable because of "not null" constrain. The only solution I've found:

insert into test values (New.id, now());
select raise(ignore);

test database for illustrative purposes only, in practice there are more complicated cases with calculated data. There may be something like this "update New set New.t = now()", or not?

like image 777
lunicon Avatar asked Feb 20 '12 05:02

lunicon


People also ask

Does an on update trigger have access to old and new variables?

An UPDATE trigger can refer to both OLD and NEW transition variables. INSERT.

What is before insert trigger?

By using triggers that run before an update or insert, values that are being updated or inserted can be modified before the database is actually modified. These can be used to transform input from the application (user view of the data) to an internal database format where desired.

Can you create the following trigger before or after update trigger for each row Yes?

A new column value can be assigned in a BEFORE row trigger, but not in an AFTER row trigger (because the triggering statement takes effect before an AFTER row trigger is fired). If a BEFORE row trigger changes the value of new .

Which method is used to insert update and modify data in SQLite?

The data modification clauses in SQLite are INSERT, UPDATE, and DELETE statements. It is used for inserting new rows, updating existing values, or deleting rows from the database.


1 Answers

No, you can't update NEW.

What I tend to do is use a VIEW with an INSTEAD OF trigger as mu is to short commented.

In your case the best solution may be to use an AFTER INSERT/UPDATE trigger WHEN NEW.t IS NULL to update t in the affected row(s):

CREATE TRIGGER test_in
AFTER INSERT ON test
FOR EACH ROW
WHEN (NEW.t IS NULL)
BEGIN
   UPDATE test SET t = now() WHERE id = NEW.id;
END;

FYI, your id column should probably be declared as INTEGER PRIMARY KEY...

like image 165
Nico Avatar answered Oct 16 '22 21:10

Nico