Can a trigger update more than one column?

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Can a trigger update more than one column?

wkranec
Hi,

There was some recent discussion about timestamp triggers, something I
have been trying to get working on my own.  Specifically, I have two
fields, insert_ts and last_upd_ts, which I would like to be the insert
time and last update time, respectively.  However, when I try to write
a trigger to populate these fields, only last_upd_ts gets populated.

I have tried two versions of the trigger:

CREATE TRIGGER task_list_1
AFTER INSERT ON task_list
        BEGIN
                UPDATE task_list
                SET insert_ts = datetime('now','localtime'),
                       last_upd_ts = datetime('now','localtime')
                WHERE task_id = NEW.task_id;
        END;

and, thinking that update might only be allowed to populate one column
at a time,

CREATE TRIGGER task_list_1
AFTER INSERT ON task_list
        BEGIN
                UPDATE task_list SET insert_ts = datetime('now','localtime') WHERE
task_id = NEW.task_id;
                UPDATE task_list SET last_upd_ts = datetime('now','localtime') WHERE
task_id = NEW.task_id;
        END;

Do I have to write a seperate trigger for each column that I want to
update?  That seems inefficient to me, so hopefully there is another
way.

Thanks for any help,

Bill
Reply | Threaded
Open this post in threaded view
|

Re: Can a trigger update more than one column?

Puneet Kishor

On Aug 21, 2005, at 9:20 AM, <[hidden email]> wrote:

> Hi,
>
> There was some recent discussion about timestamp triggers, something I
> have been trying to get working on my own.  Specifically, I have two
> fields, insert_ts and last_upd_ts, which I would like to be the insert
> time and last update time, respectively.  However, when I try to write
> a trigger to populate these fields, only last_upd_ts gets populated.

can someone explain to me why folks try to accomplish the above using
TRIGGERs when the insert time can be set so easily by simply defining
the column as

        insert_ts DATETIME DEFAULT CURRENT_TIMESTAMP

and last_upd_ts (or its equivalent) can set in the application logic
and updated during an UPDATE.

That said...

>
> I have tried two versions of the trigger:
>
> CREATE TRIGGER task_list_1
> AFTER INSERT ON task_list
> BEGIN
> UPDATE task_list
>                 SET insert_ts = datetime('now','localtime'),
>                        last_upd_ts = datetime('now','localtime')
>                 WHERE task_id = NEW.task_id;
> END;
>
> and, thinking that update might only be allowed to populate one column
> at a time,
>
> CREATE TRIGGER task_list_1
> AFTER INSERT ON task_list
> BEGIN
> UPDATE task_list SET insert_ts = datetime('now','localtime') WHERE
> task_id = NEW.task_id;
> UPDATE task_list SET last_upd_ts = datetime('now','localtime') WHERE
> task_id = NEW.task_id;
> END;
>
>

As far as I can see, both TRIGGERs above will only happen after INSERT,
and therefore, will insert exactly the same time in both insert_ts and
last_upd_ts defeating the intended purpose of the two fields.

Perhaps you want to create one AFTER INSERT and one AFTER UPDATE
TRIGGER.

Although why, I still don't understand.

--
Puneet Kishor

Reply | Threaded
Open this post in threaded view
|

Re: Can a trigger update more than one column?

wkranec
> can someone explain to me why folks try to accomplish the above using
> TRIGGERs when the insert time can be set so easily by simply defining
> the column as
>
>         insert_ts DATETIME DEFAULT CURRENT_TIMESTAMP
>
> and last_upd_ts (or its equivalent) can set in the application logic
> and updated during an UPDATE.

In practice, I have found it to be more effective to keep data-related
logic in sql rather than in any external application.  Triggers are
great for enforcing these types of rules.

The problem with using table defaults is that it does not enforce any
type of rule, sql will accept any value I give it, like '2909-12-25',
for the insert_ts field, rather than forcing it to be the actual time
of insertion.

> As far as I can see, both TRIGGERs above will only happen after INSERT,
> and therefore, will insert exactly the same time in both insert_ts and
> last_upd_ts defeating the intended purpose of the two fields.

That's what I would expect them to do.  The problem is that no time
gets inserted into insert_ts.

> Perhaps you want to create one AFTER INSERT and one AFTER UPDATE
> TRIGGER.

I have an AFTER UPDATE trigger, which doesn't do me much good if the
insert trigger isn't working.

Bill
Reply | Threaded
Open this post in threaded view
|

Re: Can a trigger update more than one column?

Puneet Kishor

On Aug 21, 2005, at 9:55 AM, <[hidden email]> wrote:

>> can someone explain to me why folks try to accomplish the above using
>> TRIGGERs when the insert time can be set so easily by simply defining
>> the column as
>>
>>         insert_ts DATETIME DEFAULT CURRENT_TIMESTAMP
>>
>> and last_upd_ts (or its equivalent) can set in the application logic
>> and updated during an UPDATE.
>
> In practice, I have found it to be more effective to keep data-related
> logic in sql rather than in any external application.  Triggers are
> great for enforcing these types of rules.
>
> The problem with using table defaults is that it does not enforce any
> type of rule, sql will accept any value I give it, like '2909-12-25',
> for the insert_ts field, rather than forcing it to be the actual time
> of insertion.

well, while true, neither you nor your application actually will be
setting the value of the insert_ts at all. It is the database that will
be doing that for you. By defining a column as

        foo DATETIME DEFAULT CURRENT_TIMESTAMP

and then _not_ explicitly INSERTing any value, you have automagically
stored your logic for the insert_ts right in the database by letting
the db do it for you. The DATETIME column type, and the DEFAULT value
facility are provided for a purpose. Use them. If the database
erroneously starts inserting typos like '2909-11-31' by itself then the
SQLite team have bigger problems on their hand.

Now you are left to worry about only the last_upd_ts.

You can now update that column either with an AFTER UPDATE TRIGGER or
within the application.

While I understand the wisdom of pushing as much data-related logic in
the database, you have the application layer for some reason after all.
You are, after all, doing an UPDATE from the application, which, you
want, then to kick-start the TRIGGER. Hence, simply doing a

        UPDATE table
        SET last_upd_ts = '<put your validated current timestamp here>'
        WHERE condition

is not that revolutionary.


--
Puneet Kishor

Reply | Threaded
Open this post in threaded view
|

Re: Can a trigger update more than one column?

D. Richard Hipp
In reply to this post by wkranec
On Sun, 2005-08-21 at 10:20 -0400, [hidden email] wrote:

> There was some recent discussion about timestamp triggers, something I
> have been trying to get working on my own.  Specifically, I have two
> fields, insert_ts and last_upd_ts, which I would like to be the insert
> time and last update time, respectively.  However, when I try to write
> a trigger to populate these fields, only last_upd_ts gets populated.
>
> I have tried two versions of the trigger:
>
> CREATE TRIGGER task_list_1
> AFTER INSERT ON task_list
> BEGIN
> UPDATE task_list
>                 SET insert_ts = datetime('now','localtime'),
>                        last_upd_ts = datetime('now','localtime')
>                 WHERE task_id = NEW.task_id;
> END;
>

Both insert_ts and last_upd_ts get updated when I try this.
What version of SQLite are you using?  What platform?  What
language binding?
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Can a trigger update more than one column?

wkranec
> Both insert_ts and last_upd_ts get updated when I try this.
> What version of SQLite are you using?  What platform?  What
> language binding?

I'm running sqlite 3.2.2 on Win XP, and I'm running things inside
sqlite itself, not another language.

Thanks for the help!

Bill
Reply | Threaded
Open this post in threaded view
|

Re: Can a trigger update more than one column?

Mark de Vries
In reply to this post by Puneet Kishor
On Sun, 21 Aug 2005, Puneet Kishor wrote:

>
> On Aug 21, 2005, at 9:20 AM, <[hidden email]> wrote:
>
> > Hi,
> >
> > There was some recent discussion about timestamp triggers, something I
> > have been trying to get working on my own.  Specifically, I have two
> > fields, insert_ts and last_upd_ts, which I would like to be the insert
> > time and last update time, respectively.  However, when I try to write
> > a trigger to populate these fields, only last_upd_ts gets populated.
>
> can someone explain to me why folks try to accomplish the above using
> TRIGGERs when the insert time can be set so easily by simply defining
> the column as
>
> insert_ts DATETIME DEFAULT CURRENT_TIMESTAMP
>
> and last_upd_ts (or its equivalent) can set in the application logic
> and updated during an UPDATE.

Because you depend on the application or person to do 'the right thing',
and/or because I don't want to want to write all those update statements
in all those apps & scripts that access the DB... MUCH easier to just do
it once in the DB....

> That said...
>
> >
> > I have tried two versions of the trigger:
> >
> > CREATE TRIGGER task_list_1
> > AFTER INSERT ON task_list
> > BEGIN
> > UPDATE task_list
> >                 SET insert_ts = datetime('now','localtime'),
> >                        last_upd_ts = datetime('now','localtime')
> >                 WHERE task_id = NEW.task_id;
> > END;
> >
> > and, thinking that update might only be allowed to populate one column
> > at a time,
> >
> > CREATE TRIGGER task_list_1
> > AFTER INSERT ON task_list
> > BEGIN
> > UPDATE task_list SET insert_ts = datetime('now','localtime') WHERE
> > task_id = NEW.task_id;
> > UPDATE task_list SET last_upd_ts = datetime('now','localtime') WHERE
> > task_id = NEW.task_id;
> > END;
> >
> >
>
> As far as I can see, both TRIGGERs above will only happen after INSERT,
> and therefore, will insert exactly the same time in both insert_ts and
> last_upd_ts defeating the intended purpose of the two fields.

That's one of the problems I ran into...

> Perhaps you want to create one AFTER INSERT and one AFTER UPDATE
> TRIGGER.

No, that does not work... Think; the AFTER INSERT trigger does what?
Which triggers? So you still end up with both collumns set after the just
the initial insert.

You can get a little facy with triggers with WHERE clauses to (try to)
fix that, but then complexity quickly rises to the point where putting it
into the application level is easier.
And if that's not an option because you can't, or don't want, to trust the
applications/scripts/users then sqlite prolly isn't the right tool for the
job....

This is the exact same question I had. And I realized that this is just a
limitation of sqlite. No problem, I will just have to do things a little
different than I'm used to. I my case there is no need to be absolutely
'secure' about the value in the these fields. And the pros of using sqlite
for the project I'm working on outweigh these cons.

Thnx to all who responded to my version of this question.

Regards,
Mark

Reply | Threaded
Open this post in threaded view
|

Re: Can a trigger update more than one column?

Mark de Vries
On Sun, 21 Aug 2005, Mark de Vries wrote:

> > > I have tried two versions of the trigger:
> > >
> > > CREATE TRIGGER task_list_1
> > > AFTER INSERT ON task_list
> > > BEGIN
> > > UPDATE task_list
> > >                 SET insert_ts = datetime('now','localtime'),
> > >                        last_upd_ts = datetime('now','localtime')
> > >                 WHERE task_id = NEW.task_id;
> > > END;
> > >
> > > and, thinking that update might only be allowed to populate one column
> > > at a time,
> > >
> > > CREATE TRIGGER task_list_1
> > > AFTER INSERT ON task_list
> > > BEGIN
> > > UPDATE task_list SET insert_ts = datetime('now','localtime') WHERE
> > > task_id = NEW.task_id;
> > > UPDATE task_list SET last_upd_ts = datetime('now','localtime') WHERE
> > > task_id = NEW.task_id;
> > > END;
> > >
> > >
> >
> > As far as I can see, both TRIGGERs above will only happen after INSERT,
> > and therefore, will insert exactly the same time in both insert_ts and
> > last_upd_ts defeating the intended purpose of the two fields.
>
> That's one of the problems I ran into...
>
> > Perhaps you want to create one AFTER INSERT and one AFTER UPDATE
> > TRIGGER.
>
> No, that does not work... Think; the AFTER INSERT trigger does what?
> Which triggers? So you still end up with both collumns set after the just
> the initial insert.

Hmm... just thinking; are you guarenteed to get the same time in this
case? Does CURRENT_TIMESTAMP get the real 'current timestamp', or the time
the transaction started?

If it is the latter then you could tell it was not modified if
insert_ts=last_upd_ts (Provided it's impossible for your app to insert &
update in the same second.) If it's always the real current time you
couldn't even be sure of that...

Rgds,
Mark