im using SQL Server 2005 need help with the below Create an

i\'m using SQL Server 2005, need help with the below

Create an Instead of trigger that handles Updates and Inserts to the Grade table by adding the values for CREATED_BY, CREATED_DATE, MODIFIED_BY, and MODIFIED_DATE for Inserts and updating the last two items for updates. Also, verify that the STUDENT_ID, SECTION_ID, and GRADE_TYPE_CODEs are legal and, if not, raise an error.

Solution

*****************Trigger for INSERT or AFTER INSERT*****************

CREATE TRIGGER triggerGradeInsert ON Grade
FOR INSERT
AS
   declare @createdby varchar(100);
   declare @modifiedby varchar(100);

   select @createdby=i.CREATED_BY from inserted i;  
   select @modifiedby=i.MODIFIED_BY from inserted i;  

   insert into Grade (CREATED_BY,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE)
   values(@createdby,getdate(),@modifiedby,getdate());

   PRINT \'INSERT data triggered\'
GO

This will be triggered when new data is inserted in to the grade table.

***************Trigger for UPDATE**********************

CREATE TRIGGER triggerGradeUpdate ON dbo.Grade
FOR UPDATE
AS
   declare @modifiedby varchar(100);
  
   select @modifiedby=i.MODIFIED_BY from inserted i;

   insert into Grade (MODIFIED_BY,MODIFIED_DATE)
   values(@modifiedby,getdate());

   PRINT \'UPDATE data triggered\'
GO

This will be triggered when data is updated from grade table.

************Check dublicate entires for unique values*************

CREATE TRIGGER cehck_dub ON Grade
AFTER INSERT
AS
BEGIN

Declare @std_id int,@sec_id int, @grade_type varchar(100)
Declare @check_sec_id int,@check_std_id int,@check_type int

select @check_std_id=Count(*) from Grade where STUDENT_ID=@std_id
select @check_sec_id=Count(*) from Grade where SECTION_ID=@sec_id
select @check_type=Count(*) from Grade where GRADE_TYPE_CODEs=@grade_type

if(@check_sec_id>1 or @check_std_id>1 or @check_type>1)
    BEGIN
        Print \'Dublicate entry, This data already exists\';
    END
else
    Commit

END