Hi, All.

How I can determine in trigger, wether was row deleted?
I need create one trigger for insert,update and delete.

Sergei.

Report this post to a moderator

Have a look at the DELETED table which is populated (it is not real just
available in a trigger) when you perform a DELETE on a table with a DELETE
trigger.


--



Allan Mitchell (Microsoft SQL Server MVP)
www.SQLDTS.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org



"sergei sheikin" <sheich@mosinfo.ru> wrote in message
news:edO#4l7lCHA.1588@tkmsftngp02...
> Hi, All.
>
> How I can determine in trigger, wether was row deleted?
> I need create one trigger for insert,update and delete.
>
> Sergei.
>
>

Report this post to a moderator

To add to Allan's response, you can check for existence of rows in the
inserted and deleted tables to determine the statement type. However,
the statement type will be unknown if no rows were affected. Example
below.

CREATE TABLE Table1
(
Col1 int NOT NULL
)
GO

CREATE TRIGGER TR1
ON TABLE1
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
DECLARE @DeletedData bit, @InsertedData bit

IF EXISTS(SELECT * FROM deleted)
SET @DeletedData = 1
ELSE
SET @DeletedData = 0

IF EXISTS(SELECT * FROM inserted)
SET @InsertedData = 1
ELSE
SET @InsertedData = 0

IF @InsertedData = 1
IF @DeletedData = 1
PRINT 'Update statement' --inserted and deleted data
ELSE
PRINT 'Insert statement' --inserted data only
ELSE
IF @DeletedData = 1
PRINT 'Delete statement' --deleted data only
ELSE
PRINT 'No rows affected - can''t determine statement'
GO

SET NOCOUNT ON

INSERT INTO Table1 VALUES(1)
UPDATE Table1 SET Col1 = 1
DELETE FROM Table1 WHERE Col1 = 1

INSERT INTO Table1
SELECT 1 FROM Table1 WHERE 0 = 1
UPDATE Table1 SET Col1 = 1 WHERE Col1 = 0
DELETE Table1 WHERE Col1 = 0
GO

DROP TABLE Table1
GO




--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Allan Mitchell" <allan@no-spam.SQLDTS.com> wrote in message
news:u2ng187lCHA.2064@tkmsftngp07...
> Have a look at the DELETED table which is populated (it is not real
just
> available in a trigger) when you perform a DELETE on a table with a
DELETE
> trigger.
>
>
> --
>
>
>
> Allan Mitchell (Microsoft SQL Server MVP)
> www.SQLDTS.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
>
>
> "sergei sheikin" <sheich@mosinfo.ru> wrote in message
> news:edO#4l7lCHA.1588@tkmsftngp02...
> > Hi, All.
> >
> > How I can determine in trigger, wether was row deleted?
> > I need create one trigger for insert,update and delete.
> >
> > Sergei.
> >
> >
>
>

Report this post to a moderator