Board index » delphi » Delete on a SQLServer View bypasses delete trigger

Delete on a SQLServer View bypasses delete trigger

Using SQLServer I have a view comprisng multiple tables and a "on delete
trigger" that ensures only one table is effected when a delete query is run
against the view.  This works fine when a delete query is called from
various SQL utilities but when a delete is called in a TADODataset using
this view it seems to bypass the trigger, determine the underlying tables
and delete the corresponding record from each table (not what I want but a
neat trick if you wanted it).  This is followed by  a message to the effect
that the data cannot be updated having been changed by another user.

Does anyone know what is happening here?  I have overcome this by
intercepting the delete and calling a stored procedure which does what the
trigger was designed to do but I shouldn't have to do this surely

Many thanks

 

Re:Delete on a SQLServer View bypasses delete trigger


Hi!
IMHO you should set 'Unique table' property to the name of the table you are
gouing to delete recods from.

Take a look at
http://mspress.microsoft.com/prod/books/sampchap/3445.htm

Also I advice you to use TbetterADODataset component - just take a look -
you woun't be sorry
http://web.orbitel.bg/vassil/

"Trevor Smith" <trev...@your-service.co.uk> ???Y/???Y ?????
???Y??: news:3b0919b8$1_1@dnews...

Quote
> Using SQLServer I have a view comprisng multiple tables and a "on delete
> trigger" that ensures only one table is effected when a delete query is
run
> against the view.  This works fine when a delete query is called from
> various SQL utilities but when a delete is called in a TADODataset using
> this view it seems to bypass the trigger, determine the underlying tables
> and delete the corresponding record from each table (not what I want but a
> neat trick if you wanted it).  This is followed by  a message to the
effect
> that the data cannot be updated having been changed by another user.

> Does anyone know what is happening here?  I have overcome this by
> intercepting the delete and calling a stored procedure which does what the
> trigger was designed to do but I shouldn't have to do this surely

> Many thanks

Re:Delete on a SQLServer View bypasses delete trigger


Trevor,

I had the same problem with INSTEAD OF triggers on a view.  The solution is
to create the view with the VIEW_METADATA option, eg.

CREATE VIEW MyView
WITH VIEW_METADATA
AS
....

Have a look at the SQLServer Books Online for more information on the
VIEW_METADATA option.

Regards
Jerrie

Quote
"Trevor Smith" <trev...@your-service.co.uk> wrote in message

news:3b0919b8$1_1@dnews...
Quote
> Using SQLServer I have a view comprisng multiple tables and a "on delete
> trigger" that ensures only one table is effected when a delete query is
run
> against the view.  This works fine when a delete query is called from
> various SQL utilities but when a delete is called in a TADODataset using
> this view it seems to bypass the trigger, determine the underlying tables
> and delete the corresponding record from each table (not what I want but a
> neat trick if you wanted it).  This is followed by  a message to the
effect
> that the data cannot be updated having been changed by another user.

> Does anyone know what is happening here?  I have overcome this by
> intercepting the delete and calling a stored procedure which does what the
> trigger was designed to do but I shouldn't have to do this surely

> Many thanks

Re:Delete on a SQLServer View bypasses delete trigger


From: "Trevor Smith" <trev...@your-service.co.uk>
Subject: Delete on a SQLServer View bypasses delete trigger
Date: 21 May 2001 2:35 PM

Using SQLServer I have a view comprisng multiple tables and a "on delete
trigger" that ensures only one table is effected when a delete query is run
against the view.  This works fine when a delete query is called from
various SQL utilities but when a delete is called in a TADODataset using
this view it seems to bypass the trigger, determine the underlying tables
and delete the corresponding record from each table (not what I want but a
neat trick if you wanted it).  This is followed by  a message to the effect
that the data cannot be updated having been changed by another user.

Does anyone know what is happening here?  I have overcome this by
intercepting the delete and calling a stored procedure which does what the
trigger was designed to do but I shouldn't have to do this surely

Many thanks

Re:Delete on a SQLServer View bypasses delete trigger


Quote
>Using SQLServer I have a view comprisng multiple tables and a "on delete
>trigger" that ensures only one table is effected when a delete query is run
>against the view.  This works fine when a delete query is called from
>various SQL utilities but when a delete is called in a TADODataset using
>this view it seems to bypass the trigger, determine the underlying tables
>and delete the corresponding record from each table

ADO  has a Unique Table property that you can set to the table you want the
delete action on.  Other tables in the Join will not be deleted.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Other Threads