Trigger Error

Transact-SQL
1

Hi team , I am not able to create trigger for following code , getting error,

create trigger dbo.EXTERNAL_TRIGGER

on dbo.EXTERNAL
after DELETE

as
begin

set nocount on

declare

@CLAIM_SOURCE char(1) ,
@CLAIM_TYPE char(1) ,
@GR_POLICY_NO char(20) ,
@TAX_ID char(9) ,
@PRIM_DIAG_CODE char(7) ,
@CPT_CODE char(7) ,
@MODIFIER_CODE char(2)

If exists(select * from deleted) and not exists(Select * from inserted)

begin
SET @Action = 'D';

  SET @ModifiedBy = SYSTEM_USER;
  
set @ModifiedDate= getdate()

 SELECT @CLAIM_SOURCE =  CLAIM_SOURCE from deleted i;
	SELECT @PRIM_DIAG_CODE  = PRIM_DIAG_CODE from deleted i; 
SELECT @CPT_CODE = CPT-4_PROC_CODE from deleted i; 
SELECT @MODIFIER_CODE = MODIFIER_CODE from deleted i;        

INSERT into PPO_FACILITYEXTERNAL_AUDIT(
									ModifiedDate,
								ModifiedBy,
								Action,
								CLAIM_SOURCE ,
								CLAIM_TYPE ,
								GR_POLICY_NO ,
								TAX_ID ,
								PRIM_DIAG_CODE ,
								CPT4 ,
								MODIFIER_CODE 
								 ) 
																		
						values (@ModifiedDate,
								@ModifiedBy,
								@Action,
								@BATCHID,
								@CLAIM_SOURCE ,
								@CLAIM_TYPE ,
								@GR_POLICY_NO ,
								@TAX_ID ,
								@PRIM_DIAG_CODE ,
								@CPT_CODE ,
								@MODIFIER_CODE 
								 );

end
end

--GO

note --  	SELECT @CPT_CODE = CPT-4_PROC_CODE from deleted i;  --in this line getting error,

please help on this.
Thanks

Rajnidas

2

For one thing, I don't see @BATCHID declared or set.

But there's a bigger problem. The trigger seems to be written under the assumption that it will only receive one row at a time. That is no guaranteed! You need to write it to handle a set of inserted or deleted items.

1 Like
3

thanks for reply gbritton ,

This columns format is not working 'CPT-4_PROC_CODE' while creating a trigger
@BATCHID declared on my system.

error message ' Msg 102, Level 15, State 1, Procedure
EXTERNAL_TRIGGER, Line 73
Incorrect syntax near '_PROC_CODE'.' .

---------------------------------------------------------- I had used after insert and delete on trigger.
create trigger dbo.EXTERNAL_TRIGGER

on dbo.EXTERNAL

after UPDATE, INSERT, DELETE

thanks

Rajnidas

4

Is that the name of your column? Since it has a hypen you need to quote it:
[CPT-4_PROC_CODE]

But you still have the other problems I mentioned. Where is @batchid declared? (not in the code you posted). and the big problem is still there.

5

Thanks a lot gbritton , its working.

Regards

Rajnidas