Create Trigger in SQL Server

You can instead check column value like

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);

Select allOpen in new window

Using constraint.

What about the Trigger?

By column constraint, we can check/validate values. Trigger can be for other purpose, like to update other tables!

I don't understand that exercise... cause a single trigger on the table Student cannot replace the foreign key relation ship.

For showing that it works correctly, I would use a Grade_FK and Student_FK table as well as Grade_TR and Student_TR, so that you can compare the results.

1) Write your test initialization scripts, which populates the grade tables and deletes old test data.
2) Write your test cases against the existing tables with the foreign key relation ship. Write positive and negative tests. The must include DELETE, INSERT and UPDATE on both tables. They INSERT and UPDATE tests must cover valid and invalid values.
3) Run the test on the existing FK tables, this shows what the triggers must do.

Hi HuaMin, I never write trigger before. If I use your way, how to write a trigger to do the update? Thanks!

Just do the three steps with the existing foreign key tables. You need to start to understand how FK works first. Then we can talk about triggers.

CAVEAT: A single trigger on table students cannot replace a FK in your given case.

D'oh?

This trigger cannot replace the foreign key relationship from the given sample...

CREATE TABLE Grade_FK (
    letter CHAR(1) PRIMARY KEY
);

CREATE TABLE Student_FK (
    ID CHAR(8) ,
    Name VARCHAR(40) ,
    Grd CHAR(1) ,
    FOREIGN KEY ( Grd ) REFERENCES Grade_FK ( letter )
);
GO

INSERT INTO dbo.Grade_FK ( letter )
VALUES ( 'A' ) ,
       ( 'B' );

INSERT INTO dbo.Student_FK ( ID ,
                             Name ,
                             Grd )
VALUES ( '1', 'Student 1', 'A' ) ,
       ( '2', 'Student 2', 'B' );
GO

--- TEST
UPDATE dbo.Grade_FK
SET    letter = 'C'
WHERE  letter = 'B';
GO

Select allOpen in new window

vs

CREATE TABLE Grade_TR (
    letter CHAR(1) PRIMARY KEY
);

CREATE TABLE Student_TR (
    ID CHAR(8) ,
    Name VARCHAR(40) ,
    Grd CHAR(1)
);
GO

CREATE TRIGGER Student_Grade_Check
ON Student_TR
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;

IF EXISTS (   SELECT 1
              FROM   inserted i
                     LEFT OUTER JOIN dbo.Grade_TR g ON g.letter = i.Grd
              WHERE  g.letter IS NULL /*letter *not* found in Grade table*/
)
BEGIN
    RAISERROR('One or more grades in the Student table are not found in the Grade table.  This is not allowed, the modification is being *cancelled*.', 16, 1);
    ROLLBACK TRANSACTION;
END;
GO

INSERT INTO dbo.Grade_TR ( letter )
VALUES ( 'A' ) ,
       ( 'B' );

INSERT INTO dbo.Student_TR ( ID ,
                             Name ,
                             Grd )
VALUES ( '1', 'Student 1', 'A' ) ,
       ( '2', 'Student 2', 'B' );

--- TEST
UPDATE dbo.Grade_TR
SET    letter = 'C'
WHERE  letter = 'B';
GO

Select allOpen in new window

Thus: write you test first.. you need often complete coverage for your tests to get all aspects of a problem tested..

I never said it could completely replace a FK.  I said it could verify whether or not a matching row in the other table was found.

Your example is for a rather bizarre action.  But one could easily add a trigger on the Grade table to prevent a grade value from being changed.

Your trigger solve the points 1 and 2, but it ignores the given core problem (*):

Need to explore the concept and implementation of foreign keys. [..] For this problem, will implement the "foreign key" requirement so remove it from the above table definition. [..] (1) [..] (2) [..]

But it was addressed to the OP, this should have been clear.

(*) or rephrased: If you don't think this is a problem, can you explain why?

I took the core problem to be the actually stated actions that need taken.

I presume the instructor is using this as a way to (1) show how to write a trigger; and (2) demonstrate how difficult it would be to replicate a true FK by using triggers instead.

If the intent were only to show how to define a FK, I presume that would have been the request, rather than specifically to write a trigger.

Also, your example violated the original set up.  You deliberately removed rows from the Grade table so you could force a failure.  With the original rows in place as described, your forced-fail does not work.

With the original rows in place as described, your forced-fail does not work.

Do you assume the grade table to be a invariant?

With the original rows in place as described, your forced-fail does not work.
Do you assume the grade table to be a invariant?

Yeah, pretty much.  A will be A, B will be B, etc..  PKs don't typically change, do they?

What is your point here?  To change the OP's requirements?  We all agree that a formal FK is overall the best way to do this.  But that doesn't mean that if the dbms happens to not support them, or if they are not allowed for whatever reason, that a trigger can't protect at least a missing grade from being INSERTed/UPDATEd.

I'd like to point out that a foreign key is a tool to ensure data integrity. This includes - I'm tempted to say - the fundamental property that it ensures that the key does not vanish, e.g. by an delete or update. That's what my posts are from the first one.

Furthermore, point 2 says that you need tests. My sample shows such a test.
Here again, the given example and/or exercise is a bad one. Tests should/must be written first.

But the given example and/or exercise is for sure not complete or it should have been chosen better. Cause grades (at least here in Germany) are invariant. The also denumerable and a small set. Thus the correct representation is not a table, but a check constraint.

And in the end, sometimes I think I (we) should take the question seriously: The OP stated the problem to be complete. In this case my answer in test would no be that trigger, but it would be: A single trigger cannot replace a foreign key relationship. I would also think that this is a trick question, because it ignores, when not a fundamental, but at least a major property of the foreign key relationship.

In the q, the original post, the table already had a FK.  They were to remove the FK and "replace" it with a trigger.  To me, that makes it clear that they knew about FKs but were demonstrating how you simulated part of the effect of a FK with a trigger.

Thus the correct representation is not a table, but a check constraint.

Some people believe that, others not.  In the case of grades, I would definitely not do that, because I know that (1) some grades are missing from the table (I, W), and (2) it will be quite useful to easily have a way to list all valid grades.  A check constraint is not the best method for either of those cases.