SQL Server Functions and triggers

Use of triggers in SQL Server

-- Check whether the specified expert record exists from the temporary table
Create Function f_expert_exist_in_temp (
@ Expertid numeric (9 ),
@ Sqltype numeric (9 ),
@ Trs_flag numeric (9)

)
Returns int
As
Begin
Declare @ RET int
Select @ ret = 0
Select @ ret = count (*) from [86_xiehe_time $ _ temp]
Where expertid = @ expertid and SQL _type = @ sqltype and trs_flag = @ trs_flag
Return @ RET
End

 

-- work experience trigger (only experts have work experience)
-- add
Create trigger [workhistory_ I _trig] on [xwcmworkhistory] For insert as
begin
-- determine whether a temporary table already exists.
declare @ nisexist int
select @ nisexist = DBO. f_expert_exist_in_temp (objid, 1, 0) from inserted
If (@ nisexist> 0)
return
-- add the statement to the temporary table if it does not exist
insert into [86_xiehe_time $ _ temp] (expertid, SQL _type, trs_flag)
select objid, 1, 0 from inserted
end

go
-- modify
Create trigger [workhistory_u_trig] on [xwcmworkhistory] for update as
begin
-- determine operation table records
declare @ nisexist int
select @ nisexist = DBO. f_expert_exist_in_temp (objid, 3, 0) from deleted
If (@ nisexist = 0)
begin
-- add to the temporary table if it does not exist
insert into [86_xiehe_time $ _ temp] (expertid, SQL _type, trs_flag)
select objid, 3, 0 from deleted
end

-- Determine whether to add data to the updated table
Select @ nisexist = DBO. f_expert_exist_in_temp (objid, 1, 0) from inserted
If (@ nisexist = 0)
Begin
Insert into [86_xiehe_time $ _ temp] (expertid, SQL _type, trs_flag)
Select objid, 1, 0 from inserted
End
End

Go
 
-- Delete
Create trigger [workhistory_d_trig] on [xwcmworkhistory] For Delete
Begin
-- Determine whether the temporary table already exists.
Declare @ nisexist int
Select @ nisexist = DBO. f_expert_exist_in_temp (objid, 3, 0) from deleted
If (@ nisexist> 0)
Return

-- If it does not exist, add it to the temporary table.
Insert into [86_xiehe_time $ _ temp] (expertid, SQL _type, trs_flag)
Select objid, 3, 0 from deleted
End

Go