创建触发器防止插入 [英] Create trigger prevent insert

问题描述

我正在尝试执行以下触发器：

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

我正在尝试创建一个触发器，该触发器仅在学分为<或==到'30'时插入一个学生。学分是int类型。

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.

尝试实现此触发器时，我遇到许多错误。我真的已经尝试了所有方法，而且即时通讯超出了选择范围。专业领域的专家能指出我正确的方向吗？

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?

推荐答案

。 .microsoft.com / en-us / library / ms189799.aspx rel = noreferrer>创建触发器 MSDN文档mentation确实可以满足您的需求：

The example "Using a DML AFTER trigger to enforce a business rule between the PurchaseOrderHeader and Vendor tables" in the CREATE TRIGGER MSDN documentation does exaclty what you're looking for:

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;

这里的键是 ROLLBACK TRANSACTION ，

编辑：这应该可以满足您的要求，但是我没有进行测试，因此您的里程可能

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
)

这样，当您在 dbo中插入记录时。结果表，该约束条件检查学生是否已通过课程，并在适当时取消插入。但是，最好在应用程序层中进行检查。

This way when you insert records in the dbo.results table 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.

这篇关于创建触发器防止插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！