Oliver Peters wrote:
> sorry: my code wasn't completely what I wanted so here again:
>
> CREATE TRIGGER IF NOT EXISTS test
> BEFORE INSERT ON "a"
> BEGIN
> INSERT INTO a(code,name,creator)
> VALUES(new."code",new."name",new."identity");
> SELECT RAISE(IGNORE);
> END;
>
> the difference is that I put new."identity" into the field "creator". This is
> the way I chose to differ between creation and altering/updating of a record
> (In case of an UPDATE I've another trigger that shoots new."identity" into
> another field. The result is a kind of record-logging the "normal" user
> should see.
>
> On the other hand I've a complete logging that writes every
> inserted/updated/deleted record in a special table. If I use a trigger in
> your suggested way
>
> CREATE TRIGGER IF NOT EXISTS test
> AFTER INSERT ON "a"
> BEGIN
> INSERT INTO a(code,name,identity)
> VALUES(new."code",new."name",new."identity");
> UPDATE a SET creator = identity, identity = NULL;
> END;
>
> I'd get 2 entries per record in the log - I'd like to avoid this. Are there
> other possibilities? Maybe I'm only a little balky? ;-)
>
Oliver,
I didn't follow your description of your problem.
If you are going to replace the value of the identity field with NULL in
your "after insert" trigger, why bother inserting it at all? Why not
simply insert the same values into the desired columns?
Instead of:
INSERT INTO a(code,name,identity) VALUES('V','abc',1);
why don't you do this:
INSERT INTO a(code,name,creator) VALUES('V','abc',1);
I think you may need to expand on your description of "shooting
new.identity into another field" to clarify what you are trying to
accomplish.
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
