problem in instead of Trigger

hi,
i am using trigger as below:
alter TRIGGER Docsdelete
ON Docs
INSTEAD OF DELETE
AS
Declare @.DeletedDocLibRowId int
declare @.Dirname nvarchar(256)
declare @.LeafName nvarchar(128)
set @.Dirname = (SELECT dirname FROM DELETED)
set @.LeafName = (SELECT leafname FROM DELETED)
set @.DeletedDocLibRowId = (SELECT doclibrowid FROM DELETED)
if(@.DeletedDocLibRowId IS NULL)
BEGIN
DELETE docs from deleted where docs.dirname = @.Dirname and
docs.leafname = @.LeafName
END
my problem is that i want to delete row in instead of trigger method,
but when i use this method, i cannot delete my row , the row seems to
be still in table even when i apply my trigger.
please anyone help me in this regard.
sathya naryanan v
narayanan@.gsdindia.comThat a often misunderstanding of triggers.
Trigers are fired per DML Operation rather than per row, so the statement
> DELETE docs from deleted where docs.dirname = @.Dirname and
> docs.leafname = @.LeafName
wont work because if you delete more than one row it will Rollback end with
an error.

> alter TRIGGER Docsdelete
> ON Docs
> INSTEAD OF DELETE
> AS
> Declare @.DeletedDocLibRowId int
> declare @.Dirname nvarchar(256)
> declare @.LeafName nvarchar(128)
> set @.Dirname = (SELECT dirname FROM DELETED)
> set @.LeafName = (SELECT leafname FROM DELETED)
> set @.DeletedDocLibRowId = (SELECT doclibrowid FROM DELETED)

> if (Select COUNT(*) from deleted where DeletedDocLibRowId IS NOT NULL) > 0
> BEGIN
Delete From sometable s--That table you want to delete from
inner join deleted d
on
s.dirname = d.dirname AND
d.leafname = d.leafname
> END
HTH, Jens Suessmeyer.
"sathya" <sathyanarayananit@.gmail.com> wrote in message
news:1122111997.898569.102710@.g43g2000cwa.googlegroups.com...
> hi,
> i am using trigger as below:
> alter TRIGGER Docsdelete
> ON Docs
> INSTEAD OF DELETE
> AS
> Declare @.DeletedDocLibRowId int
> declare @.Dirname nvarchar(256)
> declare @.LeafName nvarchar(128)
> set @.Dirname = (SELECT dirname FROM DELETED)
> set @.LeafName = (SELECT leafname FROM DELETED)
> set @.DeletedDocLibRowId = (SELECT doclibrowid FROM DELETED)
> if(@.DeletedDocLibRowId IS NULL)
> BEGIN
> DELETE docs from deleted where docs.dirname = @.Dirname and
> docs.leafname = @.LeafName
> END
> my problem is that i want to delete row in instead of trigger method,
> but when i use this method, i cannot delete my row , the row seems to
> be still in table even when i apply my trigger.
> please anyone help me in this regard.
>
> sathya naryanan v
> narayanan@.gsdindia.com
>|||On 23 Jul 2005 02:46:37 -0700, sathya wrote:

>hi,
>i am using trigger as below:
>alter TRIGGER Docsdelete
>ON Docs
>INSTEAD OF DELETE
>AS
>Declare @.DeletedDocLibRowId int
>declare @.Dirname nvarchar(256)
>declare @.LeafName nvarchar(128)
>set @.Dirname = (SELECT dirname FROM DELETED)
>set @.LeafName = (SELECT leafname FROM DELETED)
>set @.DeletedDocLibRowId = (SELECT doclibrowid FROM DELETED)
>if(@.DeletedDocLibRowId IS NULL)
>BEGIN
>DELETE docs from deleted where docs.dirname = @.Dirname and
>docs.leafname = @.LeafName
>END
>my problem is that i want to delete row in instead of trigger method,
>but when i use this method, i cannot delete my row , the row seems to
>be still in table even when i apply my trigger.
>please anyone help me in this regard.
Hi sathya,
Jens is correct that this trigger will only work for single-row deletes.
However, if you tested it with single-row deletes, I see no reason why
it would not work. Visual review of the code leads me to believe that
the row will actually be deleted if doclibrowid in the row is NULL, and
it will not be deleted if doclibrowid is anything but NULL.
That being said, it is unneeded to use the "from deleted" in the final
DELETE statement. Remove it, to save SQL Server some extra work.
And when you're busy changing things, why not go ahead and make it
multi-row proof as well. I believe that Jens missed the requirement for
doclibrowid to be NULL, so I'll show you another version. It differs
from Jens' version in two regards: it tests for doclibrowid to be NULL,
and it uses the more standard ANSI-standard DELETE FROM syntax instead
of the proprietary Transact-SQL DELETE FROM FROM syntax.
ALTER TRIGGER Docsdelete
ON docs
INSTEAD OF DELETE
AS
DELETE FROM docs
WHERE EXISTS (SELECT *
FROM deleted
WHERE deleted.dirname = docs.dirname
AND deleted.leafname = docs.leafname
AND deleted.doclibrowid IS NULL)
(untested)
If this still doesn't work, then consider:
a) using an AFTER trigger to rollback unwanted changes instead of using
an INSTEAD OF trigger to execute only the wanted changes - in my
experience, AFTER triggers often prove easier to understand and to
handle.
or
b) post a script that includes CREATE TABLE statements and INSERT
statements so that we can reproduce what you are experiencing. Add the
expected output to your script. See www.aspfaq.com/5006 for more
details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)