-------------------------
ID Student Course Marks
-------------------------
1   Andy    ASM   <NULL>
2   Bob     ASM   <NULL>
3   Andy     OB   <NULL>
4   Bob      OB   <NULL>
-------------------------

quote:

---

Originally posted by guptam

Hi,

So you want to make it after the marks are inserted you can't make any more changes correct? Then you can create a trigger on update and check if marks were entered before change if they were roll back the change.

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

---

CREATE TABLE MyTables(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Student VARCHAR(30),
Course VARCHAR(4),
MARKS DECIMAL(10,2),
EditAttempts INT DEFAULT 1 )
GO
CREATE TRIGGER dbo.Check_Marks ON MyTables
FOR UPDATE
AS
BEGIN
  IF EXISTS(SELECT * FROM INSERTED 
            WHERE INSERTED.EditAttempts > 1)
    BEGIN
      RAISERROR('Please contact Admin for Updates', 16, 1)
      ROLLBACK TRAN
    END
  UPDATE MyTables
  SET MyTables.EditAttempts = MyTables.EditAttempts + 1
  FROM INSERTED
  WHERE MyTables.ID = INSERTED.ID
END
GO

INSERT INTO MyTables(Student,Course)
SELECT 'Andy ','ASM' UNION ALL    
SELECT 'Bob','ASM' UNION ALL     
SELECT 'Andy','OB' UNION ALL      
SELECT 'Bob','OB'        

UPDATE MyTables
  SET MARKS = '92'
  WHERE Course = 'ASM' and Student = 'Andy'

SELECT * FROM MyTables

UPDATE MyTables
  SET MARKS = '90'
  WHERE Course = 'ASM' and Student = 'Andy'
--Now You get the ERROR Message

DROP TABLE MyTables

quote:

---

Originally posted by visakh16

CREATE TRIGGER	dbo.Check_Marks
ON		MyTable
AFTER		UPDATE
AS
IF EXISTS(SELECT 1 FROM INSERTED i JOIN DELETED d ON d.PK=i.PK WHERE d.Marks IS NOT NULL AND i.Marks IS NOT NULL)
BEGIN
		RAISERROR('Please contact Admin for Updates', 16, 1)
		ROLLBACK TRAN
END

PK is your primary key

---