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
naraya***@gsdindia.com

That a often misunderstanding of triggers.

Trigers are fired per DML Operation rather than per row, so the statement
won´t work because if you delete more than one row it will Rollback end with
an error.

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
HTH, Jens Suessmeyer.


Show quoteHide quote

On 23 Jul 2005 02:46:37 -0700, sathya wrote:

Show quoteHide quoteHi 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

Is there a better way to do this very simple query(s)?
SQL HELP
INSTEAD OF INSERT TRIGGER AND NULL
Trigger Help
readinf blob data in sql trigger