Very Computer

When entering a value into the field for the first time on
an Insert, the trigger does not fire.

If you modify data in the field with the trigger, the
trigger will fire.

Cannot determine why the trigger will not fire.  No errors
are generated.

Win 2000 Professional, SQL 2000 SP2

there are a few flaws with this:
1. you're testing on update(completed) which is not true when, say, you
execute this
    insert tasks(opendate) select getdate()   --completed column is not
updated

and new completed value is '01/01/01'

--
-oj
http://rac4sql.home.attbi.com

Quote:> When entering a value into the field for the first time on
> an Insert, the trigger does not fire.

> If you modify data in the field with the trigger, the
> trigger will fire.

> Cannot determine why the trigger will not fire.  No errors
> are generated.

> Win 2000 Professional, SQL 2000 SP2

During an insert, the deleted table is empty.  Your logic is dependent on the
presence of rows in the deleted table.  Under this situation, your last IF

state as opposed to TRUE).  You should also change your logic to support
multi-row inserts/updates.  Seems like all of your logic could be accomodated in
a single update statement.  Your logic is also flawed the other way if the
completed column allows NULLs and if something that was completed can be
"uncompleted" (as in a correction). Under this circumstance, the select
statement in the last IF returns NULL resulting in the unknown state again.
However, your system may not allow that but it would be best if you coded as if
it did.

And for completeness, you might want to add a comment that documents the formula
for this calculation just in case someone else has to support this later on.

Quote:>-----Original Message-----
>Just to expand on this a bit ...

>During an insert, the deleted table is empty.  Your logic
is dependent on the
>presence of rows in the deleted table.  Under this

Quote:>state as opposed to TRUE).  You should also change your
logic to support
>multi-row inserts/updates.  Seems like all of your logic

Quote:>a single update statement.  Your logic is also flawed the
other way if the
>completed column allows NULLs and if something that was
completed can be
>"uncompleted" (as in a correction). Under this

Quote:>statement in the last IF returns NULL resulting in the

Quote:>However, your system may not allow that but it would be

Quote:>it did.

>And for completeness, you might want to add a comment

Quote:>for this calculation just in case someone else has to

UPDATE TASKS
   SET Hours = CASE WHEN Completed is NULL or OpenDate is NULL
                    THEN DEFAULT
                    ELSE CAST(datediff(mi, OpenDate, Completed) as
float) +
                         COALESCE ((SELECT sum(hours) FROM tasksact
WHERE
                                  tasksact.wo_num = TASKS.wo_num), 0)
                    END
WHERE exists (select * from inserted WHERE TASKS.wo_num =
inserted.wo_num)

Things to keep in mind.  The calculation is my best guess for your
original posted trigger - read my last comment in my first post.  I
assume that wo_num is the PK for TASKS.  You should type variable names
using the same case as when they were created; it is best (imho) to be
consistent regarding case in identifiers (some are mixed case, some are
all lower, table name is all upper).  I don't know what the flow of data
is for a given row over time.  For example, can a completed task be
corrected such that it is no longer completed?  If so, review the logic
to make sure that the appropriate columns are correctly updated (in this
case, Hours is set to its default value).  Can rows be inserted or
updated in the tasksact table for a completed task? Is so, should there
be a trigger on that table which performs this type of update?

Some considerations for the future.  You could avoid all of this trigger
writing if you relied on a view.  The view would do the calculation by
using the same join (or an outer join) as above.  Note that this assumes
that the calculation can be done as written above.