|
|
Posted by Erland Sommarskog on 07/06/06 08:25
R.A.M. (r_ahimsa_m@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|