SQL T-SQL插入触发器插入，更新多个表的条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13897321/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL Insert Trigger to insert,update on if condition on multiple tables
提问by Baggerz
I'm having a few issues trying to resolve an SQL Trigger to automatically set a user to blocked & create the block record including date in another table, if their Due date is equal to a set date.
我在尝试解决 SQL 触发器以自动将用户设置为阻止并在另一个表中创建包含日期的阻止记录时遇到一些问题，如果他们的截止日期等于设定日期。
The issue is that when the trigger is set off by an insert, the print statements are executed and the insert occurs, but the insert into the table does not, or the update statement ? Can anyone explain why ?
问题是，当触发器被插入触发时，执行打印语句并发生插入，但插入表没有，或者更新语句？谁能解释为什么？
Note: Both the insert and the Update statement are fine when executed by themselves.
注意：insert 和Update 语句在自己执行时都可以。
ACCOUNT TABLE
账户表
CREATE TABLE [dbo].[Account](
[AccountNo] [int] IDENTITY(1,1) NOT NULL,
[CustomerNo] [int] NOT NULL,
[PaymentNo] [int] NULL,
[CreditNo] [int] NULL,
[BlockID] [dbo].[number] NULL,
[Balence] [dbo].[currency] NOT NULL,
[AmountDue] [dbo].[currency] NOT NULL,
[DueDate] [dbo].[dates] NULL,
[AutherisedBy] [nvarchar](50) NOT NULL,
[DateCreated] [date] NOT NULL,
BLOCKEDUSER TABLE
阻塞用户表
CREATE TABLE [dbo].[BlockedUsers](
[BlockID] [int] IDENTITY(1,1) NOT NULL,
[DateEnforced] [dbo].[dates] NOT NULL,
[Blocked] [dbo].[switch] NOT NULL,
TRIGGER
扳机
ALTER TRIGGER [dbo].[Add_Blocked_User]
ON [dbo].[Account]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
Declare @ID int
Select @ID = [AccountNo] from inserted
If(Select [DueDate] from inserted) = '2011-01-01'
INSERT INTO dbo.BlockedUsers(DateEnforced,Blocked)
VALUES (GETDATE(),1)
PRINT 'New Block Date Added'
UPDATE Account
Set BlockID = IDENT_CURRENT('BlockID')
where @ID = @ID
PRINT 'Account Blocked'
END
GO
Fully Working Example : Completed using Help Below.
完全有效的示例：使用下面的帮助完成。
ALTER TRIGGER [dbo].[Add_Blocked_User]
ON [dbo].[Account]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
Declare @ID int
Select @ID = [AccountNo] from inserted
If(Select [DueDate] from inserted)Not Between (select CONVERT(date, getdate() - 30)) And (select CONVERT(date, getdate()))
Begin
INSERT INTO dbo.BlockedUsers(DateEnforced,Blocked)
VALUES (GETDATE(),1)
PRINT 'New Block Date Added'
UPDATE Account
Set BlockID = (Select Max(BlockID) From BlockedUsers)
where [AccountNo] = (Select [AccountNo] from inserted)
PRINT 'Account Blocked'
End
END
GO
采纳答案by Andriy M
The IFstatement in Transact-SQL expects a single statement after the condition:
将IF在Transact-SQL语句要求的条件后一条语句：
IF condition
statement;
If you want to perform more than one statement in the same branch, you must enclose them in the BEGIN/END"brackets":
如果要在同一个分支中执行多个语句，必须将它们括在BEGIN/ END“括号”中：
IF condition
BEGIN
statement;
statement;
...
END;
In your trigger, only the INSERT statement executes depending on the result of the (Select [DueDate] from inserted) = '2011-01-01'condition. As for both PRINTs and the UPDATE, they execute unconditionally, i.e. after everyinsert into Account. So, you probably need to add BEGINand ENDaround INSERT, UPDATE and both PRINTs:
在触发器中，仅根据(Select [DueDate] from inserted) = '2011-01-01'条件的结果执行 INSERT 语句。对于 PRINT 和 UPDATE，它们都是无条件执行的，即在每次插入到Account. 因此，您可能需要添加BEGIN并END围绕 INSERT、UPDATE 和两个 PRINT：
...
If(Select [DueDate] from inserted) = '2011-01-01'
BEGIN
INSERT INTO dbo.BlockedUsers(DateEnforced,Blocked)
VALUES (GETDATE(),1);
PRINT 'New Block Date Added';
UPDATE Account
Set BlockID = IDENT_CURRENT('BlockID')
where @ID = @ID;
PRINT 'Account Blocked';
END;
...
回答by Brian White
You did
你做到了
FOR INSERT
You want to use
你想用
AFTER INSERT, UPDATE
FOR INSERT tells Sql Server that your trigger will entirely replace the normal insert operation. AFTER INSERT tells Sql Server to go ahead and insert the row, and then execute this code as a post-processing step.
FOR INSERT 告诉 Sql Server 你的触发器将完全取代正常的插入操作。AFTER INSERT 告诉 Sql Server 继续插入行，然后执行此代码作为后处理步骤。
