|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Row was deleted?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| sergei sheikin 2002-11-29, 9:23 am |
| Hi, All.
How I can determine in trigger, wether was row deleted?
I need create one trigger for insert,update and delete.
Sergei.
| |
| Allan Mitchell 2002-11-29, 10:23 am |
| 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.
>
>
| |
| Dan Guzman 2002-11-29, 11:23 am |
| 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.
> >
> >
>
>
|
|
|
|
|