cant create a trigger - sql server 2008

I'm trying the following :

CREATE TRIGGER checkgrade ON 
[Homework4part3].[dbo].[Enrollment]
FOR INSERT
AS
    IF (NEW.grade > 20)
    BEGIN
    grade = 3
    END

GO

and my table looks like :

Enrollment(course#, QYear, SUID, units, Grade)

dont know why this error showing :

Msg 102, Level 15, State 1, Procedure checkgrade, Line 7
Incorrect s开发者_如何学JAVAyntax near 'grade'.

I'm going to try to read between the lines a bit. Obviously I've made a best guess on the join condition. I think this is what you're trying to achieve.

CREATE TRIGGER checkgrade ON 
[Homework4part3].[dbo].[Enrollment]
FOR INSERT
AS
    UPDATE e
        SET grade = 3
        FROM inserted i
            INNER JOIN enrollment e
                ON i.coursenum = e.coursenum
                    AND i.QYear = e.QYear
                    AND I.SUID = e.SUID
        WHERE i.grade > 20

GO

I suggest you use an Instead of trigger to reduce the number of writes to the table. Using an update in the trigger will cause a delete then an insert so a total of three operations are performed on the table. Using an Instead of trigger will incur only one operation

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BaseTable]') AND type in (N'U'))
DROP TABLE [dbo].[BaseTable]
GO

CREATE TABLE BaseTable
  (BaseTableID     int PRIMARY KEY IDENTITY(1,1),
   course@ int,
   QYear int,
   SUID int,
   units int,
   Grade int
  )
GO


--Create an INSTEAD OF INSERT trigger on the table.
CREATE TRIGGER InsteadTrigger on BaseTable
INSTEAD OF INSERT
AS
BEGIN

  INSERT INTO BaseTable
       SELECT 
          course@ ,
           QYear ,
           SUID ,
           units ,
           CASE  
           WHEN Grade > 20 THEN 3
           ELSE Grade
           END
       FROM inserted
END
GO

INSERT INTO [deleteme].[dbo].[BaseTable]
           ([course@]
           ,[QYear]
           ,[SUID]
           ,[units]
           ,[Grade])
     VALUES
           (10
           ,10
           ,10
           ,10
           ,10)
Select * from BaseTable

INSERT INTO [deleteme].[dbo].[BaseTable]
           ([course@]
           ,[QYear]
           ,[SUID]
           ,[units]
           ,[Grade])
     VALUES
           (20
           ,20
           ,20
           ,20
           ,20)

Select * from BaseTable
INSERT INTO [deleteme].[dbo].[BaseTable]
           ([course@]
           ,[QYear]
           ,[SUID]
           ,[units]
           ,[Grade])
     VALUES
           (30
           ,30
           ,30
           ,30
           ,30),
           (40
           ,40
           ,40
           ,40
           ,40),
           (50
           ,50
           ,50
           ,50
           ,50)



Select * from BaseTable


BaseTableID course@ QYear   SUID    units   Grade
1   10  10  10  10  10
2   20  20  20  20  20
3   30  30  30  30  3
4   40  40  40  40  3
5   50  50  50  50  3

There are a few problems.

You need to use the Inserted meta table which covers the newly updated/inserted rows. You need to perform a proper update as the trigger executes after the update/insert, not in the middle of it.

This might be closer to what's required:

 CREATE TRIGGER checkgrade ON 
    [Homework4part3].[dbo].[Enrollment]
    FOR INSERT
    AS
    BEGIN
        SET NOCOUNT ON

        Update e
        set grade = 3
        from 
            [Homework4part3].[dbo].[Enrollment] e
            join Inserted i on e.SUID = i.SUID and e.[course#] = i.[course#]
        where i.grade > 20

    END

Note: this is untested, and I've guessed as to the primary key columns on your table.

Msg 102, Level 15, State 1, Procedure checkgrade, Line 7 Incorrect syntax near 'grade'.

This means that they syntax you used was not valid syntax. This is why several others gave you differnt syntax to try.

Msg 2108, Level 15, State 1, Procedure checkgrade, Line 7 Cannot create trigger on 'Homework4part3.dbo.Enrollment' as the target is not in the current database

This means you are trying to create a trigger on a table that doesn't exist. Or it means what is said inthis link: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/64637d9e-8080-411e-a9c6-e228c6437f30

Now let's talk some trigger basics. Firast and foremost you have to design all triggers in SQL Server to handle multiple rows inserts,updates or deletes. This means you must assume that more than one record was changed in the action.

Next triggers have two pseudo-tables available called inserted and deleted which show the values beofre the actions (the deleted table) and the value after the action (the inserted table). Updates have information in both tables while inserts only have information in inserted and deletes only have information in deleted. You use these tables to find the records you want to do something with. @Joe Stefanelli has a good example of how to use the inserted table.

The next thing you want to understand about triggers is that they add time to any action on the table and because of this they must be as fast as possible. So, no correlated subqueries, no cursors or loops should be used.

IF (SELECT GRADE FROM INSERTED) > 20
BEGIN
   UPDATE Enrollment
   SET GRADE = 3
   WHERE course = (SELECT COURSE FROM INSERTED), 
   AND QYear = (SELECT QYear FROM INSERTED),
   AND SUID = (SELECT SUID FROM INSERTED),
   AND units = (SELECT UNITS FROM INSERTED)
END

If i remember well you can't use more than one table in a update statement so @Joe Stefanelli' answer will not work as expected but the idea its correct.

Hope it works.