BEGINNER：简单的删除触发器 [英] BEGINNER: simple Delete trigger
问题描述
您好，
我正在尝试学习SQL Server。我需要写一个触发器，
根据移动类型删除文件的位置。
这是我的代码：
设置ANSI_NULLS ON
设置QUOTED_IDENTIFIER ON
go
CREATE TRIGGER [DeleteDocument]
ON [dbo]。[Documents]
删除后
AS
BEGIN
- 设置NOCOUNT ON添加以防止额外的结果集
- 干扰SELECT语句。
设置NOCOUNT ON;
IF Documenty.Movement ='''PZ''或Documents.Movement =''ZW''
DELETE FROM PositionsPZZW
WHERE Documents.Number IN（SELECT Number FROM deleted）;
如果Documents.Movement =''WZ''或Documents.Movement =''RW''
从PositionsWZRW删除
WHERE Documents.Number IN（ SELECT Number FROM deleted）;
IF Documents.Ruch =''MM''
DELETE FROM PositionsMM
WHER E Documents.Number IN（选择号码FROM已删除）;
结束
不幸的是，我收到了一些我不理解的错误：
消息4104，级别16，状态1，过程DeleteDocument，第12行
多部分标识符Documents.Movement无法绑定。
消息4104，级别16，状态1，过程DeleteDocument，第12行
多部分标识符Documents.Movement无法绑定。
消息4104，级别16，状态1，过程DeleteDocument，第13行
多部分标识符Documents.Numer无法绑定。
消息4104，级别16，状态1，过程DeleteDocument，第15行
多部分标识符Documents.Movement无法绑定。
消息4104，级别16，状态1，过程DeleteDocument，第15行
多部分标识符Documents.Movement无法绑定。
消息4104，级别16，状态1，过程DeleteDocument，第16行
多部分标识符Documents.Number无法绑定。
消息4104，级别16，状态1，过程DeleteDocument，第18行
多部分标识符Documents.Movement无法绑定。
消息4104，级别16，状态1，过程DeleteDocument，第19行
多部分标识符Dokuments.Number无法绑定。
请帮助纠正代码。
非常感谢！
/ RAM /
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/
推荐答案
如果Documents.WasDeleted位不是
设置怎么禁止删除位置？
请帮助。
/ RAM /
How to forbid deleting Positions if Documents.WasDeleted bit is not
set?
Please help.
/RAM/
RAM （r_********@poczta.onet.pl）写道：
R.A.M. (r_********@poczta.onet.pl) writes:
你好，
我正在努力学习SQL Server。我需要写一个触发器，
根据移动类型删除文件的位置。
这是我的代码：
设置ANSI_NULLS ON
设置QUOTED_IDENTIFIER ON
go
CREATE TRIGGER [DeleteDocument]
ON [dbo]。[Documents]
删除后
AS
BEGIN
- 设置NOCOUNT ON添加以防止额外的结果集
- 干扰SELECT语句。
设置NOCOUNT ON;
IF Documenty.Movement ='''PZ''或Documents.Movement =''ZW''
DELETE FROM PositionsPZZW
WHERE Documents.Number IN（SELECT Number FROM deleted）;
如果Documents.Movement =''WZ''或Documents.Movement =''RW''
从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
不幸的是，我收到了一些我不理解的错误：
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:
我理解错误，但我理解你的
触发SQL Server的操作。你似乎正在用空气来赚钱。当你说：
IF Documenty.Movement =''PZ''或Documents.Movement =''ZW''
什么是文档和文档应该是什么？也许你的意思是
如果存在（选择*
FROM FROM
WHERE移动IN（''PZ''，''' ZW''）
同样适用于
DELETE FROM PositionsPZZW
WHERE Documents.Number IN（SELECT Number FROM deleted ）;
如果你有一个文档位于PositionsPZZW，
并且这些列属于CLR UDT并且有一个名为Number的属性，那么这将编译。 br />
这真的应该是什么，我甚至都不想猜，因为我知道
没有关于PositiosnPZZW的信息。
标准建议是你发布：
o表格的CREATE TABLE语句。
o INSERT带有样本数据的声明。
o In这种情况：一个示例DELETE语句。
o给出样本的预期结果。
它还有助于更详细地描述问题。
顺便问一下，为什么有三个职位表？也许有一个很好的原因，但我怀疑应该这样做。
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
SQL Server联机丛书2005年
http：// www.microsoft.com/technet/pro...ads/books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons/books.mspx
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
2006年7月6日星期四08:25:27 +0000（UTC），Erland Sommarskog
< es **** @ sommarskog.sewrote：
On Thu, 6 Jul 2006 08:25:27 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.sewrote:
>我理解错误，但我理解SQL Server所做的一点点触发器。你似乎是在用稀薄的空气制造东西。当你说：
IF Documenty.Movement =''PZ''或Documents.Movement =''ZW''
>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''
我的意思是Documents.Movement
I meant Documents.Movement
>
什么是Documenty和Documents应该是什么？也许你的意思是
如果存在（选择*
FROM FROM
WHERE移动IN（''PZ''，''' ZW''））
>
What are Documenty and Documents supposed to be? Maybe you mean
IF EXISTS (SELECT *
FROM deleted
WHERE movement IN (''PZ'', ''ZW''))
正好
Exactly
>
同样转到
DELETE FROM PositionsPZZW
WHERE Documents.Number IN（SELECT Number FROM deleted）;
这将编译如果你有一个文档位于PositionsPZZW，
这列是一个CLR UDT，并有一个名为Number的属性。
这真的应该是什么，我甚至不想猜，因为我知道<没有关于PositiosnPZZW的信息。
>
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.
我需要：
如果存在（SELECT * FROM删除WHERE移动IN（''PZ''，''ZW''） ）
DELETE FROM PositionsPZZW
WHERE编号IN（选择编号FROM删除）;
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.
他们有不同的列描述项目。
谢谢你，你帮助了我......问题已关闭
你能帮我发帖子吗？还有一个问题？谢谢！
/ RAM /
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/
这篇关于BEGINNER：简单的删除触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
