Devart Forums

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?

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;

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.

I thought that too. Thanks