Trigger implements INSERT,DELETE,UPDATE between two tables

Requirement description: the training record office enters the record of "going out training", and after saving, synchronize the training contract to the "contract module".

Training record sheet PX_Record create trigger

Step 1. Create a new trigger: [insert_htandAL]

Step 2. When there is an INSERT,DELETE,UPDATE operation in the PeiXun_Record table, perform the INSERT,DELETE,UPDATE operation in the trigger to synchronize the relevant changes to the contract table Emp_HT. You must use the Pxr_ID field in the PX_ record table to filter the unique identity.

The execution code is as follows:

USE [XXXXX]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo]. [insert_htandAL]

ON [dbo]. [PX_Record]

For INSERT,DELETE,UPDATE

AS

BEGIN

-- 01 New Operation

If (exists (select 1 from inserted) and not exists (select 1 from deleted))

Insert into Emp_HT (Comp_Code,Ht_code,Ht_empid,Ht_class,Ht_StartDate,Ht_EndDate,Ht_OldID,Ht_Num,Ht_Status,Ht_IsZhiXing,Ht_ContinueDesc,Ht_StopDesc

, Ht_EndDesc,Ht_desc,Ht_memo,createby,createtime,updateby,updatetime,Ht_Company,Ht_Year,G_htzdr,Ht_DocPath,Ht_Template,Ht_TemplatePath,G_pxid)

Select a.Compact Code reparentEmpact coderePxcompanyempidrecovery02wereGlossxDaterecoveryGlosshxDaterecoverynullrecovery0recovery1nullrecoverynull

, null,null,Pxr_memo,a.createby,a.createtime,a.updateby,a.updatetime,null,G_qdnx,null,null,null,null,Pxr_ID

From inserted a

Inner join Emp_Base b on a.Pxr_empid=b.Emp_id

Where isnull (pxr_class,0) = 1 and Groupxxyxylic 'Yes'

END

-- 02 delete operation

If (not exists (select 1 from inserted) and exists (select 1 from deleted))

Begin

Delete from Emp_HT

Where G_pxid in (select Pxr_ID from deleted)

End

-03 update operation

If (exists (select 1 from inserted) and exists (select 1 from deleted))

Begin

Update Emp_HT set

Ht_StartDate=a.G_sxDate

Ht_EndDate=a.G_shxDate

Ht_Year=a.G_qdnx

Updateby=a.updateby

Updatetime=a.updatetime

From inserted a

Where G_pxid=a.Pxr_ID and isnull (a. PXR class1 0) = 1 and a. Gambiifpxxylic 'Yes'

End

Remarks:

Brief introduction to triggers:

Trigger is a special stored procedure, its execution is not called by the program, nor executed manually, but triggered by events. A trigger is when an operation is performed on a table. For example: update, insert, delete these operations, the system will automatically call to execute the corresponding trigger on the table.

-- query existing triggers

Select * from sys.triggers

Select * from sys.objects where type = 'TR'

Select * from sysobjects where xtype='TR'

-- View the trigger trigger event object

Select a. Typewriter girls, rep. * from sys.trigger_events a

Inner join sys.triggers b on a.object_id = b.object_id

Where b.name = 'insert_hetongandAskLeave'

-- query T-SQL text of trigger

Exec sp_helptext 'insert_hetongandAskLeave'

-- disable triggers

Disable trigger trigger_Stu_InsteadOf on Student;-- trigger_Stu_InsteadOf trigger name

-- enable triggers

Enable trigger trigger_Stu_InsteadOf on Student;-- trigger_Stu_InsteadOf trigger name