About the use of triggers in SQLServer2000--multi-line data submission

There is a lot of controversy about the use of triggers.
The advantage of the trigger is that it enhances the verification capability of the data and can effectively implement complex business logic. Go farther than constraints and check to a certain extent.
On the downside of triggers, the most typical is that the use of triggers can degrade the performance of the system, the controllability of data, especially cross-table detection, as well as the possibility of trigger recursion more deepening the difficulty and controllability of data maintenance.

This article is not intended to discuss the principles and merits of triggers, and is intended to describe how triggers are designed for batch data submissions.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS on
GO

CREATE TRIGGER [tri_xxx] on XXX
For Insert,update
As
SET NOCOUNT on

DECLARE
--Detection flag setting
@CHECK INT,
--Define public variables
@TODAY DATETIME
--Define base table variables
@field1 INT,
@field2 INT,
@field3 VARCHAR (20),
@fieldN INT,
--Define cross-table variables
@table1_field1 INT,
@table1_field2 INT,
@tableN_fieldM INT
--Defining cursors
DECLARE cur_xxx CURSOR for
SELECT field1,field2,field3,fieldn from INSERTED
--If the non-collection is modified, it is defined as
/*
SELECT @field1 =field1,
@field2 =field2,
@field3 =field3,
@field4 =field4,
From INSERTED
*/

SET @TODAY =convert (VARCHAR), GETDATE (), 120)

OPEN cur_zygd
FETCH NEXT from Cur_xxx to @field1, @field2, @field3, @fieldN

while (@ @FETCH_STATUS =0)
BEGIN

------------------------------------------------------------------------
--------------------the following for different rules, the relevant processing--------------------
------------------------------------------------------------------------

--to meet certain conditions, do not process the direct exit
IF Cond1
BEGIN
CLOSE Cur_xxx--Non-cursors, no need for this statement
Deallocate cur_xxx--Non-cursors, no need for this statement
RETURN
END

--to meet certain conditions, then prompt processing of direct exit
IF Cond2
BEGIN
RAISERROR (' xxx error! ', 16, 1)
ROLLBACK TRAN
CLOSE Cur_xxx--Non-cursors, no need for this statement
Deallocate cur_xxx--Non-cursors, no need for this statement
RETURN
END

--cross-table check, meet certain conditions, then prompt processing of direct exit
SELECT TOP 1 @table1_field1 =field1 from table1 WHERE 1=1
IF Cond3 is not NULL
BEGIN
RAISERROR (' xxx error! ', 16, 1)
ROLLBACK TRAN
CLOSE Cur_xxx--Non-cursors, no need for this statement
Deallocate cur_xxx--Non-cursors, no need for this statement
RETURN
ELSE
BEGIN
SET @[email Protected]_field1--The current value may be updated based on other cross-table results
END

--finally update the relevant values based on the primary key
UPDATE xxx
SET [email protected],
[Email protected]
WHERE [email protected] and (ISNULL (field1, ') <>isnull (@field1, ') OR ISNULL (field2,0) <>isnull (@field2, 0 ))

FETCH NEXT from Cur_xxx to @field1, @field2, @field3, @fieldN

END

CLOSE cur_xxx
Deallocate cur_xxx

SET NOCOUNT OFF

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS on
GO

About the use of triggers in SQLServer2000--multi-line data submission