Am Montag, den 22.06.2009, 17:39 -0600 schrieb Dennis Cote:
> 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
Hello Dennis
I want the "normal" user only identify himself by putting his id into
the field identity and afterwards let the system decide in what field to
put his id (INSERT = creator, UPDATE = editor). Doing this for every
record I can show who created it and who was the last editor.
This task could be accomplished by a combination of INSERT and an AFTER
INSERT Trigger
/* Code */
INSERT INTO a(code,name,identity)
VALUES(new."code",new."name",new."identity");
CREATE TRIGGER IF NOT EXISTS test
AFTER INSERT ON "a"
BEGIN
UPDATE a SET creator = identity, identity = NULL; -- NULL to empty it for other
possible editors
END;
But because I log every insert/update/delete into a separate table too the
combination of INSERT and AFTER INSERT trigger would lead to 2 log records (1.
INSERT, 2. UPDATE) - and that's what I'd like to avoid.
Greetings
Oliver Peters
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
