SQL 创建触发器防止插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4255730/
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
Create trigger prevent insert
提问by Fredrick
I'm trying to execute the following trigger:
我正在尝试执行以下触发器：
create trigger t23
on studies
after insert, update, delete
as
begin
REFERENCING NEW ROW NewStudent
FOR EACH ROW
WHEN (30 <= (SELECT SUM(credits) FROM Studies)
DELETE FROM NewStudent N
WHERE N.spnr = NewStudent.spnr
end
I'm trying to create a trigger which only inserts a student if the credits is < or == to '30'. The "Credits" is a type int.
我正在尝试创建一个触发器，该触发器仅在学分 < 或 == 为“30”时才插入学生。“Credits”是一个int类型。
I'm getting numerous errors trying to implement this trigger. I really have tried everything and i m out of options. Could someone who is expert in the field point me in the right direction?
我在尝试实现这个触发器时遇到了很多错误。我真的已经尝试了一切，但我别无选择。该领域的专家能否为我指明正确的方向？
回答by Albireo
The example "Using a DML AFTER trigger to enforce a business rule between the PurchaseOrderHeader and Vendor tables" in the CREATE TRIGGERMSDN documentation does exaclty what you're looking for:
CREATE TRIGGERMSDN 文档中的示例“使用 DML AFTER 触发器在 PurchaseOrderHeader 和 Vendor 表之间实施业务规则”确实满足了您的要求：
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).
CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
DECLARE @creditrating tinyint, @vendorid int;
IF EXISTS (SELECT *
FROM Purchasing.PurchaseOrderHeader p
JOIN inserted AS i
ON p.PurchaseOrderID = i.PurchaseOrderID
JOIN Purchasing.Vendor AS v
ON v.BusinessEntityID = p.VendorID
WHERE v.CreditRating = 5
)
BEGIN
RAISERROR ('This vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;
The key here is ROLLBACK TRANSACTION, just adapt the example to suit your need and you're done.
这里的关键是ROLLBACK TRANSACTION，只需调整示例以满足您的需要，您就完成了。
Edit: This should accomplish what you're looking for, but I have not tested it so your mileage may vary.
编辑：这应该可以完成您正在寻找的内容，但我尚未对其进行测试，因此您的里程可能会有所不同。
create trigger dbo.something after insert as
begin
if exists ( select * from inserted where sum(credits) > 30 )
begin
rollback transaction
raiserror ('some message', 16, 1)
end
end
Another edit, based on some assumptions (please note I wrote this script on the fly since I can't test it right now):
另一个编辑，基于一些假设（请注意，我即时编写了此脚本，因为我现在无法对其进行测试）：
create table dbo.students
(
student_id int not null,
name varchar (50) not null
)
create table dbo.courses
(
course_id int not null,
name varchar (50) not null,
required_credits int not null
)
create table dbo.results
(
student_id int not null,
course_id int not null,
course_result int not null
)
create trigger dbo.check_student_results on dbo.results after insert as
(
declare @check int
select @check = count(*)
from inserted as a
join dbo.courses as b on b.course_id = a.course_id
where b.required_credits > a.course.result
if @check <> 0
begin
rollback transaction
raiserror('The student did not pass the course.', 16, 1)
end
)
This way when you insert records in the dbo.resultstable the constraint checks if the student has passed the course, and cancels the insertion if appropriate. However, it's better to check this things in the application layer.
这样，当您在dbo.results表中插入记录时，约束会检查学生是否通过了课程，并在适当时取消插入。但是，最好在应用程序层检查这些东西。
