Re: [sqlite] modifying insert/updat data in triggers (was: manipulating new.? in triggers)

[Jay Sprenkle]

> Now, whenever I insert into this table I want to set date_create to
> CURRENT_TIMESTAMP.
> 
> Whenever I update a row I want date_lch (lch=last change) to be set to
> CURRENT_TIMESTAMP. (Changes to the date_create col should be (silently)
> 'ignored'.

If you feel you cannot trust applications to correctly set the date
and time stamp
you're effectively creating a 'read only' column. This isn't possible
with most sql
engines because they don't have column level permissions. You probably want
to put the date and time stamp in a separate related table and grant no modify
permissions to that table.

Re: [sqlite] modifying insert/updat data in triggers (was: manipulating new.? in triggers)

[Kurt Welgehausen]

> create trigger r1 after insert on foo begin
>   update foo set date_create=current_timestamp where rowid=new.rowid;
> end;
> create trigger r2 after update on foo begin
>   update foo set date_lch=current_timestamp where rowid=new.rowid;
> end;
>
> -- 
> D. Richard Hipp <[EMAIL PROTECTED]>
>

create trigger r2 after update on foo begin
  update foo set date_lch=current_timestamp where rowid=new.rowid;
  update foo set date_create=old.date_create where rowid=new.rowid;
 -- so the user can't change it
end;

I believe that's what he's after.

Regards

Re: [sqlite] modifying insert/updat data in triggers (was: manipulating new.? in triggers)

[D. Richard Hipp]

On Thu, 2005-08-18 at 22:26 +0200, Mark de Vries wrote:
> create table foo (
>   value TEXT,
>   date_create TEXT,
>   date_lch TEXT
> );
> 
> Now, whenever I insert into this table I want to set date_create to
> CURRENT_TIMESTAMP.
> 
> Whenever I update a row I want date_lch (lch=last change) to be set to
> CURRENT_TIMESTAMP. (Changes to the date_create col should be (silently)
> 'ignored'.
> 

create trigger r1 after insert on foo begin
  update foo set date_create=current_timestamp where rowid=new.rowid;
end;
create trigger r2 after update on foo begin
  update foo set date_lch=current_timestamp where rowid=new.rowid;
end;

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] modifying insert/updat data in triggers (was: manipulating new.? in triggers)

[Mark de Vries]

On Thu, 18 Aug 2005, Kurt Welgehausen wrote:

> > Is it possible to change the values of certain rows that
> > are inserted into the database? ...
>
> I think everything you need is explained at
>
> .

Yeah, I've read it.

> If you don't understand how to get the current date in
> SQLite, look at the wiki page.
>

This too.

But I still don't know how to do what I want to do. Perhaps I need to
explain with a litle more detail what I mean. Consider the following
table.

create table foo (
  value TEXT,
  date_create TEXT,
  date_lch TEXT
);

Now, whenever I insert into this table I want to set date_create to
CURRENT_TIMESTAMP.

Whenever I update a row I want date_lch (lch=last change) to be set to
CURRENT_TIMESTAMP. (Changes to the date_create col should be (silently)
'ignored'.

This is exactly what the oracle trigger from my first mail does. Basically
any user specified value for these columns is always ignored as the values
to be inserted are modified by the trigger before the insert happens.

Can someone tell me if someting simmilar is or isn't possible with
triggers sqlite? And if it is possible an example of how to do it?

If it isn't possible I'll just push this logic to the application level.
No real biggy. It's just that (for larger databases) I always try to push
as many 'business rules' & logic into the database. This makes the data
better protected from application level errors/bug and saves (duplicate)
code in the apps accessing the database.

Regards,
Mark