sql server - SQL Update Trigger with If Else logic with Multiple Rows -

sql server - SQL Update Trigger with If Else logic with Multiple Rows -

i'm having problem getting trigger function multiple records while containing if/else logic need conduct test before inserting records.

my goal here enable front end end user of scheme alter event type between values 1,2, , 3, if alter event type 1 must come in occurrence date otherwise transaction rolled back. if come in occurrence date transaction entered. below logic works 1 record updated through front end end, problem when multiple changes written via sql receive error:

"msg 512, level 16, state 1, procedure tg_tbl_le_event_occurence_date_validation, line 9 subquery returned more 1 value. not permitted when subquery follows =, !=, <, <= , >, >= or when subquery used expression. statement has been terminated. "

i know trigger can rewritten business relationship multiple records, cannot find way write if else test still functions. input appreciated.

alter trigger [dbo].[tg_tbl_le_event_occurence_date_validation] on [dbo].[tbl_le_event] after update declare @insertid int set @insertid = (select event_id inserted) if ( select count(*) inserted inserted.occurence_date null , inserted.event_type_id = 1) > 0 begin rollback tran raiserror('#####occurence date required field type 1 events. please come in occurence date or alter not saved#####',10,1) end else update dbo.tbl_le_event set dbo.tbl_le_event.event_type_id = inserted.event_type_id, dbo.tbl_le_event.occurence_date = inserted.occurence_date inserted bring together dbo.tbl_le_event on dbo.tbl_le_event.event_id = @insertid

go

i able resolve error occurred multiple records. seems code did not using variables id, i'm including revisions below:

create trigger [dbo].[tg_tbl_le_event_occurence_date_validation] on [dbo].[tbl_le_event] after update if (select count (*)from inserted inserted.occurence_date null , inserted.event_type_id = 1) >0 begin rollback tran raiserror('#####occurence date required field loss events. please come in occurence date or alter not saved#####',10,1) end else update dbo.tbl_le_event set dbo.tbl_le_event.event_type_id = inserted.event_type_id, dbo.tbl_le_event.occurence_date = inserted.occurence_date inserted bring together dbo.tbl_le_event on dbo.tbl_le_event.event_id = inserted.event_id go

sql-server if-statement triggers multiple-records