Oliver Peters wrote: > > 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. > Are the users entering the SQL directly? If not, it still seems to me that your application can take the value from the Identity field in your UI and assign it to the creator field when inserting a new record. The users are seeing a different UI for insertions and update aren't they? Your application knows whether it is doing an insert or an update, so it can execute the appropriate SQL Statement.
> 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; > > Based on this I would guess that your update trigger does something like this. BEGIN UPDATE a SET editor = identity, identity = NULL; -- NULL to empty it for other possible editors END; What is the purpose of the identity field if it is always NULL after an insert or update? > 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. > > If you insist on your current approach, you could drop the insert trigger and use a field specific update trigger to log the updates done by the insert and update triggers. If the log entries are different for the two types of changes, then you could use two different triggers, or use a select case... conditional to build the appropriate log entry. CREATE TRIGGER IF NOT EXISTS log_entry AFTER UPDATE OF creator, editor ON "a" BEGIN --Insert log record for newly inserted or updated record INSERT INTO log ... END; HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
