Charly Caulet <[EMAIL PROTECTED]>
wrote:
I have a table tab(UniqueID INTEGER PRIMARY KEY, champ1 TIMESTAMP).
And
when a date ("YYYY-MM-DD") is inserted in champ1, I would like to
convert
it to timestamp.
I would like to modify new.champ1 value in a "BEFORE" trigger, but
everything I tryed doesn't work :
**this trigger can't be created : error message 'SQL error : near
"new" :
syntax error'
-------------------
CREATE TRIGGER trig_insert BEFORE INSERT ON tab FOR EACH ROW
WHEN (new.champ1 LIKE "%-%")
BEGIN
new.champ1=(SELECT strftime("%s", new.champ1));
END;
The line between BEGIN and END is not a valid SQL statement. There are
no variables in SQL, and no assignment operators.
Try this:
CREATE TRIGGER trig_insert BEFORE INSERT ON tab
WHEN (new.champ1 LIKE "%-%")
BEGIN
insert into tab(champ1) values (strftime("%s", new.champ1));
select RAISE(IGNORE);
END;
I'm not sure whether or not RAISE(IGNORE) for the first row will abort
the whole statement though, in case of a multi-row insert (as in INSERT
INTO ... SELECT ...). If the above doesn't work, this should:
CREATE TRIGGER trig_insert AFTER INSERT ON tab
WHEN (new.champ1 LIKE "%-%")
BEGIN
update tab set champ1= strftime("%s", new.champ1)
where UniqueID=new.UniqueID;
END;
**This trigger is well created, but when it executes, I have this
error
message : 'SQL error: no such table: main.new'
-------------------
CREATE TRIGGER trig_insert BEFORE INSERT ON tab FOR EACH ROW
WHEN (new.champ1 LIKE "%-%")
BEGIN
UPDATE new SET champ1=(SELECT strftime("%s", new.champ1)) WHERE
UniqueID=new.UniqueID;
END;
'new' is not a table you can update (or insert into, or delete or select
from).
So do you know if it is possible to modify new columns ?
It is important to me to modify them in a "BEFORE" trigger, because
if I
do it in an "AFTER" trigger, I would have to use an UPDATE query and
it
would lead to a deadlock (I also have some triggers on the UPDATE
statement...).
A deadlock? In a single-threaded operation? How exactly do you expect
this to happen?
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
