Is there a way to check a Trigger?

[LucieLastic]

hi

Currently, when my app(written in Delphi 5 using TQueries and cached updates) posts a change to the database, the trigger isn't firing, well I don't think it is. If I edit the table directly (eg. from Borlands SQL Explorer), the trigger fires. Is there anyway of checking if a trigger has attempted to fire and if so, why it failed?

big ta
lou
fyi, I'm using SqlSvr 7. Here's the trigger (I think it works fine in it's own right - think it's something to do with my app).

CREATE TRIGGER [TR_U_SERAUD] ON [Series]
FOR UPDATE
AS
DECLARE @vcErrorMessage varchar(255)
, @BeginTransCount int

SELECT @BeginTransCount = @@TRANCOUNT
BEGIN TRANSACTION

INSERT Series_audit
(mod_id
,modDT
,modType
,UserID
,Series_id
,Season_id
,On_Sale_Date
,Flight_Type_id
,Flight_Frequency_id
,Day_of_week_id
,Tos1,Tos2,Tos3,Tos4)
SELECT d.mod_id
,d.modDT
,'U'
,d.UserID
,d.Series_id
,d.Season_id
,d.On_Sale_Date
,d.Flight_Type_id
,d.Flight_Frequency_id
,d.Day_of_week_id
,d.Tos1, d.Tos2, d.Tos3, d.Tos4
FROM deleted d
JOIN inserted i ON i.Series_id = d.Series_id
WHERE IsNull( i.Mod_id, '') <> IsNull( d.Mod_id, '')

IF @@Error <> 0
BEGIN
SELECT @vcErrorMessage = &quot;Error: Unable to write Update audit record for Series.&quot;
GOTO error
END

error:

IF @vcErrorMessage IS NULL
BEGIN
IF @@TRANCOUNT > @BeginTransCount COMMIT TRANSACTION
END
ELSE
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
RAISERROR ( @vcErrorMessage, 16, 1)
END

 

[Tim1]

Hiya,

Why don't you place a

PRINT 'Trigger attempting to fire' statement just before the begin transaction command.

If that prints to your screen / log file, then you know the trigger is at least attempting to work, if it does not, then there is something else wrong....for example, are you sure that it has been created successfully??

HTH

Tim

 

[tlbroadbent]

It is difficult to return a message from a trigger to an application. Returning messages may cause failure of the trigger. In fact, it is recommended that you place SET NOCOUNT ON to inhibit the insert row count from displaying.

Are you running the update from the application with the same login as used in SQL Explorer? If not, does the login used have update/insert permissions on both tables? Terry

http://members.home.net/tlbroadbent/index.htm