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

[Derrell . Lipman]

Mark de Vries <[EMAIL PROTECTED]> writes:

> 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'.

I think this is what you're looking for.

CREATE TRIGGER foo_insert_tr
  AFTER INSERT
  ON foo
  BEGIN
UPDATE foo
  SET date_create = CURRENT_TIMESTAMP
  WHERE ROWID = new.rowid;
  END;

CREATE TRIGGER foo_update_tr
  AFTER UPDATE
  ON foo
  BEGIN
UPDATE foo
  SET date_lch = CURRENT_TIMESTAMP
  WHERE ROWID = new.rowid;
  END;

Derrell

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

[Puneet Kishor]

Puneet Kishor wrote:

I am jumping in the middle here, but...

Mark de Vries wrote:


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.




Set DEFAULT CURRENT_TIMESTAMP above for date_lch

  

/date_lch/date_create/

You know what I mean ;-)





CREATE TABLE foo (
  value TEXT,
  date_create DATETIME DEFAULT CURRENT_TIMESTAMP,
  date_lch DATETIME
);



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'.



Don't include the date_create column in the UPDATE statement.

UPDATE foo
SET value = 'whatever', date_lch = 'current datetime value'
WHERE your constraint

What's the problem?

Re: [sqlite] modifying insert/updat data in triggers

[Puneet Kishor]

I am jumping in the middle here, but...

Mark de Vries wrote:

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.



Set DEFAULT CURRENT_TIMESTAMP above for date_lch

CREATE TABLE foo (
  value TEXT,
  date_create DATETIME DEFAULT CURRENT_TIMESTAMP,
  date_lch DATETIME
);



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'.


Don't include the date_create column in the UPDATE statement.

UPDATE foo
SET value = 'whatever', date_lch = 'current datetime value'
WHERE your constraint

What's the problem?

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