sql - Create trigger prevent insert

Question

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.

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?

Answer 1

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;

The key here is ROLLBACK TRANSACTION, just adapt the example to suit your need and you're done.

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.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.