This did the trick!

CREATE TRIGGER [TABLENAME_T] ON [TABLENAME]
FOR UPDATE AS
DECLARE @AIA VARCHAR(8), @AIB SMALLDATETIME
SELECT @AIA = AIA, @AIB = AIB FROM INSERTED
IF ( @AIA = 'CLOSED' AND @AIB IS NULL )
BEGIN
RAISERROR('Closed on date not provided for a closed item',
15, 1)
ROLLBACK TRANSACTION
END
--
Sankar


-----Original Message-----
From: John Thomas [mailto:[Email Address Removed]
Sent: Thursday, October 30, 2003 10:31 AM
To: Khrishnamurthy, Sankar; LazyDBA.com Discussion
Subject: RE: Ensure that a column value is supplied in MS SQL Server


Hi,

Why wouldn't the after trigger work for you? There is one more type of
Triggers called "Instead of" Triggers. Books online has more
information.

John

-----Original Message-----
From: Khrishnamurthy, Sankar [mailto:sankar.[Email Address Removed]
Sent: Thursday, October 30, 2003 10:03 AM
To: LazyDBA.com Discussion
Subject: Ensure that a column value is supplied in MS SQL Server

Hi,
Is there something similar to ORACLE before trigger in SQL Server.

Here is what I want to do.

If column A is updated with 'CLOSED', column B needs to be filled with a
non null value. How can I ensure this using a trigger? Column A is
defined with not null and check constraints. Column B has no
constraints whatsoever.
--
Sankar

MS Sql Server LazyDBA home page