BEGINNER: simple Delete trigger

[R.A.M.]

Hello,
I am trying to learn SQL Server. I need to write a trigger which
deletes positions of the document depending on the movement type.
Here's my code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE TRIGGER [DeleteDocument]
ON [dbo].[Documents]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'
DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Movement = 'WZ' OR Documents.Movement = 'RW'
DELETE FROM PositionsWZRW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Ruch = 'MM'
DELETE FROM PositionsMM
WHERE Documents.Number IN (SELECT Number FROM deleted);
END

Unfortunatelly I receive errors which I don't understand:

Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 12
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 12
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 13
The multi-part identifier "Documents.Numer" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 15
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 15
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 16
The multi-part identifier "Documents.Number" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 18
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 19
The multi-part identifier "Dokuments.Number" could not be bound.

Please help to correct the code.
Thank you very much!
/RAM/

[R.A.M.]

How to forbid deleting Positions if Documents.WasDeleted bit is not
set?
Please help.
/RAM/

[Erland Sommarskog]

R.A.M. (r_********@poczta.onet.pl) writes:

Hello,
I am trying to learn SQL Server. I need to write a trigger which
deletes positions of the document depending on the movement type.
Here's my code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE TRIGGER [DeleteDocument]
ON [dbo].[Documents]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'
DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Movement = 'WZ' OR Documents.Movement = 'RW'
DELETE FROM PositionsWZRW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Ruch = 'MM'
DELETE FROM PositionsMM
WHERE Documents.Number IN (SELECT Number FROM deleted);
END

Unfortunatelly I receive errors which I don't understand:

I understand the errors, but I understand about as little of your
trigger that SQL Server does. You seem to be making things up out of
thin air. When you say:

IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'

What are Documenty and Documents supposed to be? Maybe you mean

IF EXISTS (SELECT *
FROM deleted
WHERE movement IN ('PZ', 'ZW'))
The same goes for

DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);

This would compile if you have a column Documents in PositionsPZZW,
and this columns is of a CLR UDT and had an attribute named Number.
What this really should be, I don't even want to guess, since I know
nothing about PositiosnPZZW.

The standarad recommendation is that you post:

o CREATE TABLE statements for your tables.
o INSERT statments with sample data.
o In this case: a sample DELETE statement.
o The desired result given the sample.

It also helps to give a little more detailed description of the problem.

By the way, why are there three Positions tables? Maybe there is a good
reason for this, but I have a suspicion that one should do.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[R.A.M.]

On Thu, 6 Jul 2006 08:25:27 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.sewrote:

>I understand the errors, but I understand about as little of your
trigger that SQL Server does. You seem to be making things up out of
thin air. When you say:

IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'

I meant Documents.Movement

>
What are Documenty and Documents supposed to be? Maybe you mean

IF EXISTS (SELECT *
FROM deleted
WHERE movement IN ('PZ', 'ZW'))

Exactly

>

The same goes for

DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);

This would compile if you have a column Documents in PositionsPZZW,
and this columns is of a CLR UDT and had an attribute named Number.
What this really should be, I don't even want to guess, since I know
nothing about PositiosnPZZW.

I need:
IF EXISTS (SELECT * FROM deleted WHERE Movement IN ('PZ', 'ZW'))
DELETE FROM PositionsPZZW
WHERE Number IN (SELECT Number FROM deleted);

>By the way, why are there three Positions tables? Maybe there is a good
reason for this, but I have a suspicion that one should do.

They have different columns describing items.

Thank you, you have helped me... Problem closed
Could you help me with post "one more question"? Thank you!
/RAM/

[R.A.M.]

Sorry, too short problem description.
Anyway, I solved.
/RAM/

[rcamarda]

R.A.M.,
What was the solution you found? Please post as others might have a
simular problem.
TIA
Rob

R.A.M. wrote:

Sorry, too short problem description.
Anyway, I solved.
/RAM/

[Roy Harvey]

On Thu, 06 Jul 2006 10:46:50 +0200, R.A.M. <r_********@poczta.onet.pl>
wrote:

>IF EXISTS (SELECT * FROM deleted WHERE Movement IN ('PZ', 'ZW'))
DELETE FROM PositionsPZZW
WHERE Number IN (SELECT Number FROM deleted);

That looks dangerous. If one row in DELETED has a 'PZ' value, all
rows in PositionsPZZW that match DELETED will be dropped, even those
that do NOT have 'PZ' or 'ZW'.

How about this alternative:

DELETE FROM PositionsPZZW
WHERE Number IN
(SELECT Number FROM deleted WHERE Movement IN ('PZ', 'ZW'));

It does not require the IF test at all, as if there are no matches it
will do nothing.

Roy Harvey
Beacon Falls, CT

[Erland Sommarskog]

R.A.M. (r_********@poczta.onet.pl) writes:

Could you help me with post "one more question"? Thank you!

If you repost it, and clarify what you mean. I understood very little
of it.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

But the code implies some design problems. What are the logical
differences among
PositionsPZZW, PositionsWZRW and PositionsMM ? This looks like
attribute splitting.

Why are you using triggers instead of DRI actions?

[R.A.M.]

[R.A.M.]

On 6 Jul 2006 04:39:13 -0700, "rcamarda" <ro*****@hotmail.comwrote:

>What was the solution you found? Please post as others might have a
simular problem.
TIA
Rob

I decided not to use WasDeleted flag in Documents, so it was enough to
set Delete Rule in FK_Positions_Documents to "No Action".
/RAM/