I haven't used triggers much before, doing most of the work in Delphi. However, now I am starting to.

I am finding it hard to debug triggers.

* When I insert/update a row in a table is it possible to see whether the Trigger executed? (is there some sort of log I can use to track this)

* Is it possible to see whether the trigger failed or generated an error?

--

As an example, I have a fairly simple trigger, when a row is added to a controlling table and the "PeopleID" is updated

CREATE TRIGGER "PeoplePassword" AFTER UPDATE OF "PeopleID" ON "StaffPasswords"
WHEN NewRow.PeopleID IS Not NULL
BEGIN
 EXECUTE IMMEDIATE
   ' UPDATE People
     SET Password = ''*****'',
         SecurityLevelID = 0
     WHERE ID = '+ CAST(NewRow.PeopleID as VARCHAR)+'
     AND (Password is NULL or Password = '''') ';
END

This seems pretty straight forward, but adding a record to StaffPasswords, and updating the PeopleID to a value in the "people" table does not result in the People table being updated.

The update statement runs fine outside of the trigger, so I am not sure of the reason for the problem.

Sorry, I figured this one out. I need an AFTER INSERT trigger as well as a AFTER UPDATE trigger for this to work.

It seems that the AFTER UPDATE trigger is not called when a newly inserted record is posted, I guess I should have made sense of that ...

On 1/14/2014 3:33 AM, Adam Brett wrote:
> Sorry, I figured this one out. I need an AFTER INSERT trigger as well as a AFTER UPDATE trigger for this to work.
>
> It seems that the AFTER UPDATE trigger is not called when a newly inserted record is posted, I guess I should have made sense of that ...

Yes - there is a separate trigger for INSERT, UPDATE and DELETE as well
as before/after version. Or if you're doing the exact same thing then
you can create an ALL trigger - but then you need to use the operation
function to check what op it is and run for insert/update only for example.

For log I usually end up creating a simple logging table myself and just
do an insert from triggers and functions and such. In theory you can use
SET LOG MESSAGE as well though have not tried it myself.

Raul

Raul,

I debug Triggers the same way you do, using an additional log table with 3 columns (ID, DateTime, Message) and a stored procedure to insert log records, that I call inside triggers when debugging.

SET LOG MESSAGE doesn't have the same functionality - it only fires a TEDBLogMessageEvent that can be used from inside applications (EDB Manager can also sow the log messages) and Triggers *do not* fire those events.
 
--
Fernando Dias
[Team Elevate]

Adam,

<< * When I insert/update a row in a table is it possible to see whether the
Trigger executed? (is there some sort of log I can use to track this)

* Is it possible to see whether the trigger failed or generated an error? >>

I added this for 2.15 B3, which is coming out tomorrow.  With B3, you'll be
able to use SET LOG/STATUS MESSAGE along with an OnLog/StatusMessage event
handler for the TEDBTable to trap/view such messages, and the ElevateDB
Manager now does the same.   In addition, these same trigger messages will
also bubble up for queries, stored procedures, etc.

Tim Young
Elevate Software
www.elevatesoft.com

Just another note about this:  it wasn't mentioned anywhere, so don't look
for it in the release notes, etc.

Officially, I'm not supposed to be adding breaking changes like this to new
builds.

Tim Young
Elevate Software
www.elevatesoft.com

Damn it, now I have to upgrade to 2.15