1

My trigger is meant to calcalute the grade of a student base on the column value for every insert.But if a column value for the column is modified it the corresponding Grade should change. Currently i having error:"Invalid object name StudentMarks for each code section.
I did some search to the cause of the error but still can find one. Please advise

        -- ================================================
    -- Template generated from Template Explorer using:
    -- Create Trigger (New Menu).SQL
    --
    -- Use the Specify Values for Template Parameters 
    -- command (Ctrl-Shift-M) to fill in the parameter 
    -- values below.
    --
    -- See additional Create Trigger templates for more
    -- examples of different Trigger statements.
    --
    -- This block of comments will not be included in
    -- the definition of the function.
    -- ================================================
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:      TSQL PROGRAMMER
    -- Create date: 
    -- Description: Calcute studnet Grade
    -- =============================================
    CREATE TRIGGER dbo.ComputeGrade 
       ON  dbo.StudentMarks 
       AFTER INSERT,UPDATE
       declare @markobtained  int =(Select MarkObtained from inserted i )
    AS 
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        if(@markobtained between 0 and 49)
        BEGIN
        UPDATE StudentMarks SET Grade='Fail'
        END 
        ELSE IF (@markobtained between 50  and 64)
        BEGIN
        UPDATE StudentMarks SET Grade='Fair'
        END
       ELSE IF(@markobtained between 65 and 74)
       BEGIN
       UPDATE StudentMarks SET Grade ='Good'
       END
       ELSE IF(@markobtained between 75 and 84)
       BEGIN
       UPDATE StudentMarks SET Grade='Very Good'
       END
       ELSE IF(@markObtained between 85 and 100)
       BEGIN
       UPDATE StudentMarks SET Grade ='Excellent'
       END
       ELSE

       PRINT'Invalid mark range'


     END
    GO
  • Move your DECLARE inside the trigger definition. Also, there will be many, many problems with what you are trying to do here anyway, I suspect. Look into trying to write this set-based if it's for a school project, or use a computed column instead of a trigger. – Avarkx Nov 24 '14 at 15:10
  • @Avarkx: did that and i now got this error.Msg 8197, Level 16, State 4, Procedure ComputeGrade, Line 6 The object 'dbo.StudentMarks' does not exist or is invalid for this operation – lern Nov 24 '14 at 15:20
  • any would you mine indicate the like problems you think these can cause? – lern Nov 24 '14 at 15:22
  • @Avarkx,I realised i was running my code in the master.Now is working – lern Nov 24 '14 at 15:34
  • Your trigger has MAJOR flaw in that you seem to assume it'll be called once per row - that is not the case. The trigger will fire once per statement, so if your UPDATE statements affects 25 rows, you'll get the trigger fired once, but then Inserted and Deleted will each contain 25 rows. WHich of those 25 rows will your code select here?? SELECT DiscountPercent FROM Inserted - it's non-deterministic. You need to rewrite your trigger to take this into account! – marc_s Nov 25 '14 at 13:26
3

While the syntax of your trigger is not quite correct, this is not the source of the error. The main problem...

Msg 8197, Level 16, State 4, Procedure ComputeGrade
The object 'dbo.StudentMarks' does not exist or is invalid for this operation.

...is exactly what the error message states. Either you have spelled the name of the table wrong, you are in the wrong database, or it is in a schema other than dbo. To locate it, make sure you are in the right database, then run:

SELECT s.name, t.name
  FROM sys.schemas AS s
  INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
  WHERE t.name LIKE N'StudentMark%';

Now, on to the trigger. The main problem is this: which row do you expect to process in the case of a multi-row insert or update? Assigning a value from inserted to a variable assigns exactly one value. Which one will it be? The first one? The last one? Random? You should re-write it using a set-based operation, for example:

CREATE TRIGGER dbo.ComputeGrade 
   ON  dbo.StudentMarks 
   AFTER INSERT,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    UPDATE m
      SET Grade = CASE
        WHEN MarkObtained < 50 THEN 'Fail'
        WHEN MarkObtained < 65 THEN 'Fair'
        WHEN MarkObtained < 75 THEN 'Good'
        WHEN MarkObtained < 85 THEN 'Very Good'
        ELSE 'Excellent' END
   FROM dbo.StudentMarks AS m
   INNER JOIN inserted AS i
   ON m.[key column] = i.[key column];
END

But even better than a trigger would be a computed column.

ALTER TABLE dbo.StudentMarks DROP COLUMN Grade;

ALTER TABLE dbo.StudentMarks ADD Grade AS
  CONVERT(VARCHAR(10), CASE
        WHEN MarkObtained < 50 THEN 'Fail'
        WHEN MarkObtained < 65 THEN 'Fair'
        WHEN MarkObtained < 75 THEN 'Good'
        WHEN MarkObtained < 85 THEN 'Very Good'
        ELSE 'Excellent' END);

This way there is no trigger to maintain (or keep enabled) and all data is automatically synced at all times.

Not the answer you're looking for? Browse other questions tagged or ask your own question.