I'm having difficulties with the UPDATE statement in my trigger.  The trigger is fired after DELETE on the sysdba.ACCOUNT table.  It INSERTs to an audit table that basically tracks deleted accounts.  There is a similar auditing table for changes to contacts, and I would like to update this table to reflect that its parent account has been deleted.

CREATE TRIGGER [sysdba].[tr_AccountDelete]<br /> ON [sysdba].[ACCOUNT]
 AFTER DELETE
 AS
 
 IF @@ROWCOUNT > 0
 BEGIN
     SET NOCOUNT ON
     DECLARE @Date DATETIME
     SET @Date = GETDATE()
 
     INSERT INTO Audits
     (id, table_name, date, row_deleted)
     SELECT  ACCOUNTID,
             'ACCOUNT',
             GETDATE(),
             'ACCOUNTID:' + acct.ACCOUNTID + ' ACCOUNT:' + ISNULL(acct.ACCOUNT, '') + ' MAINPHONE:' + ISNULL(acct.MAINPHONE, '')
     FROM DELETED AS acct;
     
     UPDATE ContactAudits
     SET parent_state = 'DELETED' WHERE parent_id IN (SELECT ACCOUNTID FROM DELETED)
 END
 SET NOCOUNT OFF

The INSERT statement should be free of errors.  As far as I can tell the UPDATE statement is the problem since the trigger will succeed without it.

Aside from the fact that triggers are not supported by the SLX framework, I'm sure you'll appreciate that telling us you're 'having difficulties' is not really providing sufficient diagnostics.

I'd suggest that you try removing the semi-colon first.

I'd also suggest you look at the @Date variable: what is it for?

If that does not resolve it, please post the text of the error.

I should elaborate on the source of the error.

Creating the trigger causes no problems; the problem arises during execution. When deleting an account, for instance, the account is not deleted.  This indicates that something has gone wrong during the execution of the trigger and SLX has rolled back the transaction.  I'm lead to believe that the UPDATE statement is the problem because when it is removed from the trigger, deletion proceeds merrily.  I don't know why it would have a problem with the UPDATE statement, but it seems to be the source of trouble.

Unfortunately, I've got no error text for you because SalesLogix doesn't provide any for this type of problem.  All I have to go on is trial and error.  I have a feeling that the problem is something I don't know about SLX or an obvious bug in my SQL that I'm overlooking.

Before my original post I tried with/without @Date and/or the semi-colon.

Thanks for any ideas you've got.

EDIT/UPDATE: It was indeed some obvious bug in my SQL, since I hadn't granted UPDATE permissions on the foreign table to sysdba.  I checked permissions before but glazed over adding UPDATE.  (frowny-face)

Oops! Ah well, got there in the end