Problem With Trigger

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
swright
Posts: 28
Joined: Wed 23 Dec 2009 12:19

Problem With Trigger

Post by swright » Tue 23 Mar 2010 16:35

I have a table with an update trigger to delete records where two linking values are null, when I attempt to update the table

tblLotLevies.Edit;
tblLotLevies.FieldByName('Levy Name').AsString := 'TEST';
tblLotLevies.Post;

I get the error message

"Update Failed. Found 0 records"

The table has the following Structure

CREATE TABLE [dbo].[LOTLEVIES] (
[idLotLevy] INTEGER IDENTITY(1,1) NOT NULL,
[idLOT] INTEGER,
[idBookingDetail] INTEGER,
[Levy Name] CHAR(20) COLLATE Latin1_General_CI_AS NOT NULL,
[Levy Type] INTEGER NOT NULL,
[Levy Amount] FLOAT(53) NOT NULL,
[Vat Code] CHAR(1) COLLATE Latin1_General_CI_AS NOT NULL,
[Nominal Code] CHAR(30) COLLATE Latin1_General_CI_AS,
CONSTRAINT [PK__LTL__idLotLevy] PRIMARY KEY CLUSTERED ([idLotLevy])
)
CREATE NONCLUSTERED INDEX [IX__LTL__idLOT__idLotLevy] ON [dbo].[LOTLEVIES] ([idLOT] ASC,[idLotLevy] ASC)
CREATE NONCLUSTERED INDEX [IX__LTL__Levy_Name__idLotLevy] ON [dbo].[LOTLEVIES] ([Levy Name] ASC,[idLotLevy] ASC)


ALTER TABLE [dbo].[LOTLEVIES] ADD CONSTRAINT [FK__LTL__LOT__idLot]
FOREIGN KEY ([idLOT]) REFERENCES [dbo].[LOT] ([idLOT]) ON DELETE SET NULL
ALTER TABLE [dbo].[LOTLEVIES] ADD CONSTRAINT [FK__LTL__LTP__Levy_Type]
FOREIGN KEY ([Levy Type]) REFERENCES [dbo].[LEVYTYPES] ([Levy Type])
ALTER TABLE [dbo].[LOTLEVIES] ADD CONSTRAINT [FK__LTL__VAT__Vat_Code]
FOREIGN KEY ([Vat Code]) REFERENCES [dbo].[VAT CODES] ([Vat Code])
ALTER TABLE [dbo].[LOTLEVIES] ADD CONSTRAINT [FK__LTL__BKDT_idBookingDetail]
FOREIGN KEY ([idBookingDetail]) REFERENCES [dbo].[BOOKING DETAIL] ([idBookingDetail]) ON DELETE SET NULL

CREATE TRIGGER [TR__LTL__CASCADE__idLot__idBookingDetail] ON"[dbo].[LOTLEVIES] FOR UPDATE AS DELETE [dbo].[LOTLEVIES] WHERE [idLot] IS NULL AND [idBookingDetail] IS NULL;


Am I doing something Wrong?

swright
Posts: 28
Joined: Wed 23 Dec 2009 12:19

Post by swright » Tue 23 Mar 2010 17:28

After looking further it seems there are two ways around this which would you recommend?

First set _UseUpdateOptimization := True; and then re-enable after post.

Or tbtLotLevies.Options.StrictUpdate := False; and then re-enable after post;

Dimon
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 24 Mar 2010 13:56

Try to use the __UseUpdateOptimization variable. However in some cases it cannot help, there can be triggers that snap into action both before modification and after modification. In this case you should set the TMSTable.Options.StrictUpdate property to False.

swright
Posts: 28
Joined: Wed 23 Dec 2009 12:19

Post by swright » Wed 24 Mar 2010 15:48

I thought that too. Thanks

Post Reply