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)

Problem in instead of trigger

hi,
i am using instead of trigger in sqlserver 2005, my trigger looks like
create trigger [dbo].[docsUpdate]
on [dbo].[Docs]
instead of update
as
IF (Update(MetaInfo))
BEGIN
bla bla
bla bla
[Some operation]
end
my table looks lke this
Dirname LeafName TimeLastModified Extension Metainfo
-- -- -- -- --
if any data in metainfo column updated then my trigger will get fired
and do respective operation, but when dirname , leafname or any if
other column get modified other them metainfo then how these modified
data will get affected in to my docs table.
Since changes done on table will not get affected due to instead of
trigger, how can i update other column values.
please help me.
thanks
sathya narayanan
narayanan@.gsdindia.com
http://www.microsoft.com/communities...7-cdfb17b609f8
AMB
"sathya" wrote:

> hi,
>
> i am using instead of trigger in sqlserver 2005, my trigger looks like
>
> create trigger [dbo].[docsUpdate]
> on [dbo].[Docs]
> instead of update
> as
> IF (Update(MetaInfo))
> BEGIN
> bla bla
> bla bla
> [Some operation]
> end
> my table looks lke this
>
> Dirname LeafName TimeLastModified Extension Metainfo
> -- -- -- -- --
>
> if any data in metainfo column updated then my trigger will get fired
> and do respective operation, but when dirname , leafname or any if
> other column get modified other them metainfo then how these modified
> data will get affected in to my docs table.
> Since changes done on table will not get affected due to instead of
> trigger, how can i update other column values.
> please help me.
>
> thanks
> sathya narayanan
> narayanan@.gsdindia.com
>