mssql 触发器 insert update delete

创建一个有insert update delete 的触发器 关于mssql
疑问：不知道mssql中的游标有没有类似oracle中的游标的type%tablename的功能请各位赐教？

1.插入操作（Insert）
Inserted表有数据，Deleted表无数据

2.删除操作（Delete）
Inserted表无数据，Deleted表有数据

3.更新操作（Update）
Inserted表有数据（新数据），Deleted表有数据（旧数据）
Create Trigger tr_PCard
    On P_Card
    for Insert,Update,Delete

    if   exists (select * from inserted) AND exists (select * from deleted)    --如果UPDATE执行一下程序
    begin
        return
    end

    if  exists(select * from inserted) and not exists(select * from deleted)    --如果INSERT执行一下程序
    begin
     return       
    end

    if exists(select * from deleted) and not exists (select * from inserted)
    begin
        return
    end
AFCD 中的一个例子：

USE [AFCD]
GO
/****** Object:  Trigger [dbo].[Pooled_Result]    Script Date:
02/08/2011 09:50:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Pooled_Result]

ON [dbo].[RESULT]

FOR INSERT,UPDATE

AS
--INSERTED
IF exists(select * from inserted) and not exists(select * from deleted)
BEGIN
	DECLARE @testNumber INTEGER
	DECLARE @sample INTEGER
	DECLARE @idText VARCHAR(30)
	DECLARE @temp VARCHAR(30)
	DECLARE @ParSamp INTEGER
	DECLARE @count INTEGER
	DECLARE @compName VARCHAR(50)
	DECLARE @resultValue INTEGER
	DECLARE @resultText VARCHAR(200)
	DECLARE @parTestNumber INTEGER

	DECLARE TEST_NUM cursor for SELECT TEST_NUMBER FROM INSERTED


	OPEN TEST_NUM
	FETCH next FROM TEST_NUM into @testNumber
	WHILE @@fetch_status = 0
	BEGIN
 
	  SELECT @sample = (SELECT SAMPLE FROM TEST WHERE TEST_NUMBER = @testNumber)

	  IF charindex(@idText, 'P') < 9
		 BEGIN
		 RETURN
	  END
	FETCH next FROM TEST_NUM into @testNumber
	END
	--INSERT INTO RESULT VALUES( '6492','Virus Isolation',2,'O','',0,0,0,'','','','AV_HI',0,'No haemagglutinating agent',0,'F','E','','2011-02-10 11:16:41.000','LENA',NULL,'','',0,0,0,'','x',0,0,'F','F',NULL,NULL,1,0,NULL,NULL,0,'',0,NULL,0,NULL,'F','')
	CLOSE TEST_NUM
	DEALLOCATE TEST_NUM

	--SELECT PARENT ID_NUMERIC
	  DECLARE ID_NUM CURSOR FOR SELECT ID_NUMERIC FROM SAMPLE WHERE CHILD_SAMPLE = @sample AND IS_PARENT = 'T'
	  --declare one param to get the parent id_number
	  DECLARE @ParIdNum varchar(10) 
	  OPEN ID_NUM
	  FETCH next FROM ID_NUM into @ParIdNum
	  WHILE @@FETCH_STATUS = 0
	  BEGIN

		  DECLARE @ParTestNum VARCHAR(10)
		  SELECT @ParTestNum = (SELECT test_number  FROM test WHERE SAMPLE = @ParIdNum )
		  DECLARE RESULT_RECORD CURSOR FOR SELECT R.* FROM TEST T ,RESULT R WHERE T.TEST_NUMBER = R.TEST_NUMBER AND T.SAMPLE = @sample
			DECLARE @TEST_NUMBER	varchar(10)
			DECLARE @NAME	varchar(40)	
			DECLARE @ORDER_NUMBER	varchar(10)	
			DECLARE @RESULT_TYPE	varchar(1)	
			DECLARE @UNITS	varchar(10)	
			DECLARE @MINIMUM	float	
			DECLARE @MAXIMUM	float	
			DECLARE @TYPICAL	float	
			DECLARE @TRUE_WORD	varchar(10)	
			DECLARE @FALSE_WORD	varchar(10)	
			DECLARE @ALLOWED_CHARACTERS	varchar(26)	
			DECLARE @CALCULATION	varchar(10)	
			DECLARE @VALUE	float	
			DECLARE @TEXT	varchar(234)	
			DECLARE @PLACES	int	
			DECLARE @OUT_OF_RANGE	varchar(1)	
			DECLARE @STATUS	varchar(1)	
			DECLARE @OLD_STATUS	varchar(1)	
			DECLARE @ENTERED_ON	datetime	
			DECLARE @ENTERED_BY	varchar(10)	
			DECLARE @DATE_AUTHORISED	datetime	
			DECLARE @AUTHORISER	varchar(10)	
			DECLARE @GROUP_ID	varchar(10)	
			DECLARE @SECURITY_FLAGS	int	
			DECLARE @SIG_FIGS_NUMBER	int	
			DECLARE @SIG_FIGS_ROUNDING	int	
			DECLARE @SIG_FIGS_FILTER	varchar(10)	
			DECLARE @REP_CONTROL	varchar(3)	
			DECLARE @MINIMUM_PQL	float	
			DECLARE @MAXIMUM_PQL	float	
			DECLARE @LESS_THAN_PQL	varchar(1)	
			DECLARE @GREATER_THAN_PQL	varchar(1)	
			DECLARE @PQL_CALCULATION	varchar(10)	
			DECLARE @FORMULA	VARCHAR(2000)	
			DECLARE @DILUTION_FACTOR	float	
			DECLARE @SPIKE_VALUE	float	
			DECLARE @SURROGATE_MLP	varchar(10)	
			DECLARE @SURROGATE_MLP_VERSION	varchar(10)	
			DECLARE @MATRIX_NO	int	
			DECLARE @MATRIX_NAME	varchar(10)	
			DECLARE @COLUMN_NO	int	
			DECLARE @COLUMN_NAME	varchar(20)	
			DECLARE @ROW_NO	int	
			DECLARE @ROW_NAME	varchar(20)	
			DECLARE @HAS_INCIDENTS	varchar(1)	
			DECLARE @INSTRUMENT_USED	varchar(10) 
			OPEN RESULT_RECORD
			FETCH next FROM RESULT_RECORD into @TEST_NUMBER,@NAME,@ORDER_NUMBER,@RESULT_TYPE,@UNITS,@MINIMUM,@MAXIMUM,@TYPICAL,@TRUE_WORD,@FALSE_WORD,@ALLOWED_CHARACTERS,@CALCULATION,@VALUE,@TEXT,@PLACES,@OUT_OF_RANGE,@STATUS,@OLD_STATUS,@ENTERED_ON,@ENTERED_BY,@DATE_AUTHORISED,@AUTHORISER,@GROUP_ID,@SECURITY_FLAGS,@SIG_FIGS_NUMBER,@SIG_FIGS_ROUNDING,@SIG_FIGS_FILTER,@REP_CONTROL,@MINIMUM_PQL,@MAXIMUM_PQL,@LESS_THAN_PQL,@GREATER_THAN_PQL,@PQL_CALCULATION,@FORMULA,@DILUTION_FACTOR,@SPIKE_VALUE,@SURROGATE_MLP,@SURROGATE_MLP_VERSION,@MATRIX_NO,@MATRIX_NAME,@COLUMN_NO,@COLUMN_NAME,@ROW_NO,@ROW_NAME,@HAS_INCIDENTS,@INSTRUMENT_USED
			WHILE @@FETCH_STATUS = 0
			BEGIN
			 DECLARE @TEST_NUM_FLAG VARCHAR(10)
             DECLARE @ANALYSIS_CHILD VARCHAR(10) 
             DECLARE @ANALYSIS_PARENT VARCHAR(10)
			 SELECT @TEST_NUM_FLAG  = ( SELECT TEST_NUMBER FROM RESULT WHERE TEST_NUMBER = @ParTestNum AND NAME = @NAME )
             
             SELECT @ANALYSIS_CHILD = (SELECT ANALYSIS FROM TEST WHERE SAMPLE = @sample)
             SELECT @ANALYSIS_PARENT = (SELECT ANALYSIS FROM TEST WHERE SAMPLE = @ParIdNum)
			 IF (@TEST_NUM_FLAG IS NULL ) AND (@ANALYSIS_CHILD = @ANALYSIS_PARENT)
			 BEGIN
			 INSERT INTO RESULT VALUES (@ParTestNum,@NAME,@ORDER_NUMBER,@RESULT_TYPE,@UNITS,@MINIMUM,@MAXIMUM,@TYPICAL,@TRUE_WORD,@FALSE_WORD,@ALLOWED_CHARACTERS,@CALCULATION,@VALUE,@TEXT,@PLACES,@OUT_OF_RANGE,@STATUS,@OLD_STATUS,@ENTERED_ON,@ENTERED_BY,@DATE_AUTHORISED,@AUTHORISER,@GROUP_ID,@SECURITY_FLAGS,@SIG_FIGS_NUMBER,@SIG_FIGS_ROUNDING,@SIG_FIGS_FILTER,@REP_CONTROL,@MINIMUM_PQL,@MAXIMUM_PQL,@LESS_THAN_PQL,@GREATER_THAN_PQL,@PQL_CALCULATION,@FORMULA,@DILUTION_FACTOR,@SPIKE_VALUE,@SURROGATE_MLP,@SURROGATE_MLP_VERSION,@MATRIX_NO,@MATRIX_NAME,@COLUMN_NO,@COLUMN_NAME,@ROW_NO,@ROW_NAME,@HAS_INCIDENTS,@INSTRUMENT_USED)
			 END
			 FETCH next FROM RESULT_RECORD into @TEST_NUMBER,@NAME,@ORDER_NUMBER,@RESULT_TYPE,@UNITS,@MINIMUM,@MAXIMUM,@TYPICAL,@TRUE_WORD,@FALSE_WORD,@ALLOWED_CHARACTERS,@CALCULATION,@VALUE,@TEXT,@PLACES,@OUT_OF_RANGE,@STATUS,@OLD_STATUS,@ENTERED_ON,@ENTERED_BY,@DATE_AUTHORISED,@AUTHORISER,@GROUP_ID,@SECURITY_FLAGS,@SIG_FIGS_NUMBER,@SIG_FIGS_ROUNDING,@SIG_FIGS_FILTER,@REP_CONTROL,@MINIMUM_PQL,@MAXIMUM_PQL,@LESS_THAN_PQL,@GREATER_THAN_PQL,@PQL_CALCULATION,@FORMULA,@DILUTION_FACTOR,@SPIKE_VALUE,@SURROGATE_MLP,@SURROGATE_MLP_VERSION,@MATRIX_NO,@MATRIX_NAME,@COLUMN_NO,@COLUMN_NAME,@ROW_NO,@ROW_NAME,@HAS_INCIDENTS,@INSTRUMENT_USED
			END
			CLOSE RESULT_RECORD
			DEALLOCATE RESULT_RECORD
			FETCH next FROM ID_NUM into @ParIdNum
	  END
	  CLOSE ID_NUM
	  DEALLOCATE ID_NUM

END

 --UPDATED
IF exists (select * from inserted) AND exists (select * from deleted)
BEGIN
    
	DECLARE @NEW_TEXT VARCHAR(234)

	DECLARE @R_TEXT VARCHAR(234)
	DECLARE @R_TEST_NUM INTEGER
	DECLARE @R_NAME VARCHAR(40)
	--TO GET THE OLD DATA FROM RESULT 
	SELECT @R_TEXT = (SELECT TEXT FROM deleted)
	SELECT @R_TEST_NUM = (SELECT TEST_NUMBER FROM deleted)
	SELECT @R_NAME = (SELECT NAME FROM deleted)

	--TO GET THE NEW DATA FROM RESULT
	SELECT @NEW_TEXT = (SELECT TEXT FROM inserted)

	DECLARE @T_SAMPLE INTEGER
	DECLARE @Par_id_num varchar(10)
	SELECT @T_SAMPLE  = (SELECT SAMPLE FROM TEST WHERE TEST_NUMBER = @R_TEST_NUM)

	DECLARE ID_NUMS_C CURSOR FOR SELECT ID_NUMERIC FROM SAMPLE WHERE CHILD_SAMPLE = @T_SAMPLE AND IS_PARENT = 'T'
	OPEN  ID_NUMS_C
	FETCH NEXT FROM ID_NUMS_C INTO @Par_id_num
	WHILE @@fetch_status = 0
	BEGIN
	DECLARE @T_NUMER VARCHAR(10)
	SELECT @T_NUMER = (SELECT TEST_NUMBER FROM TEST WHERE SAMPLE = @Par_id_num)
    DECLARE @ANA_CHILD VARCHAR(10)
    DECLARE @ANA_PARENT VARCHAR(10)
    SELECT @ANA_CHILD = (SELECT ANALYSIS FROM TEST WHERE SAMPLE = @T_SAMPLE)
    SELECT @ANA_PARENT = (SELECT ANALYSIS FROM TEST WHERE SAMPLE = @Par_id_num)
    IF @ANA_CHILD = @ANA_PARENT
     BEGIN
	 UPDATE RESULT SET TEXT = @NEW_TEXT where TEST_NUMBER = @T_NUMER AND NAME = @R_NAME
     END
	FETCH NEXT FROM ID_NUMS_C INTO @Par_id_num
	END
    CLOSE ID_NUMS_C
	DEALLOCATE ID_NUMS_C


END
    
RETURN