Trigger doesnt get fired everytime

[psabale]

Hi,
I have created the foll trigger for data validation on a table 'STMTSPRQ' :
[ Logic for trigger : IF one field of the table is set to 'N' , then the other field should be NULL. If not , then an error should be thrown back to the user]

CREATE TRIGGER triggerForSTMTS PRQ ON [dbo].[STMTSPRQ]
FOR INSERT, UPDATE
AS
DECLARE @IsrtInd VARCHAR(1),
@SpclHndlReasCD VARCHAR(4)

SELECT @IsrtInd=ISRT_I ND,
@SpclHndlReasCD =SPCL_HNDL_REAS _CD
FROM STMTSPRQ

IF (@ISRT_IND ='N' AND @SpclHndlReasCD <>NULL)
begin
RAISERROR('Spec ial Handling Reason Code should be NULL for ISRT_IND=N',16, 1)
ROLLBACK TRANSACTION
end

The problem is that it works only when i try to update the last record or insert a new record. It doesnt work if i try to update other existing records
Any help would be appreciated

[Jim Doherty]

Hi,
I have created the foll trigger for data validation on a table 'STMTSPRQ' :
[ Logic for trigger : IF one field of the table is set to 'N' , then the other field should be NULL. If not , then an error should be thrown back to the user]

CREATE TRIGGER triggerForSTMTS PRQ ON [dbo].[STMTSPRQ]
FOR INSERT, UPDATE
AS
DECLARE @IsrtInd VARCHAR(1),
@SpclHndlReasCD VARCHAR(4)

SELECT @IsrtInd=ISRT_I ND,
@SpclHndlReasCD =SPCL_HNDL_REAS _CD
FROM STMTSPRQ

IF (@ISRT_IND ='N' AND @SpclHndlReasCD <>NULL)
begin
RAISERROR('Spec ial Handling Reason Code should be NULL for ISRT_IND=N',16, 1)
ROLLBACK TRANSACTION
end

The problem is that it works only when i try to update the last record or insert a new record. It doesnt work if i try to update other existing records
Any help would be appreciated

Where triggers are concerned you need to be looking at the special tables called inserted and deleted and using the IF UPDATE statement logic. Have a look at this previous thread to give you an idea

http://www.thescripts.com/forum/thread81100.html

Jim :)