trigger help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

trigger help

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-26 : 04:08:07

[code]
CREATE TRIGGER TR_INSERT_EMRGROUPMASTER ON EMRGROUPMASTER FOR INSERT
AS
BEGIN
DECLARE
@currentGroupID NUMERIC(20,0),
@currentScreenImmunID NUMERIC(20,0),
@currentScreenImmunMasterID NUMERIC(20,0),
@currentScreenImmunRecordType VARCHAR(2),
@seedScreenImmunID NUMERIC(20,0),
@updateQuery1 VARCHAR(4000),
@updateQuery2 VARCHAR(4000),
@insertQuery VARCHAR(4000),
@propertyName VARCHAR(50),
@SCREEN_IMMUN_ID NUMERIC(20,0),
@RECORD_TYPE VARCHAR(2),
@GENDER NUMERIC(20,0),
@SUB_GENDER NUMERIC(20,0),
@DESCRIPTION VARCHAR(500),
@STARTING_AGE_OPERATOR VARCHAR(255),
@STARTING_AGE_VALUE NUMERIC(20,0),
@STARTING_AGE_UNITS VARCHAR(255),
@FREQUENCY VARCHAR(500),
@STARTING_AGE_LIMIT_RANGE NUMERIC(20,0),
@STARTING_AGE_LIMIT_UNITS VARCHAR(255),
@AGE_END_LIMIT NUMERIC(20,0),
@AGE_END_LIMIT_UNITS VARCHAR(255),
@REPEAT INT,
@REPEAT_AGE_VALUE NUMERIC(20,0),
@REPEAT_AGE_UNITS VARCHAR(255),
@FREQUENCY_LIMIT NUMERIC(20,0),
@REPEAT_CONDITION VARCHAR(255),
@GROUP_ID NUMERIC(20,0),
@STATUS NUMERIC(5,0),
@CDC_CODE VARCHAR(50),
@SCREEN_COMMENTS VARCHAR(255),
@MASTER_REFERENCE_ID NUMERIC(20,0),
@Result varchar(8000),
@FetchCount INT,
@ezEMRxScreenImmunRecords CURSOR
BEGIN
SET @Group_id=(select GROUP_ID FROM INSERTED)
SELECT @currentScreenImmunID = ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME = 'SCREEN_IMMUN_ID'
SET @ezEMRxScreenImmunRecords = CURSOR FAST_FORWARD FOR
SELECT SCREEN_IMMUN_ID, RECORD_TYPE,GENDER,SUB_GENDER,DESCRIPTION,STARTING_AGE_OPERATOR,
STARTING_AGE_VALUE,STARTING_AGE_UNITS,FREQUENCY,STARTING_AGE_LIMIT_RANGE,STARTING_AGE_LIMIT_UNITS,
AGE_END_LIMIT,AGE_END_LIMIT_UNITS,REPEAT,REPEAT_AGE_VALUE,REPEAT_AGE_UNITS,FREQUENCY_LIMIT,
REPEAT_CONDITION, STATUS,CDC_CODE,SCREEN_COMMENTS,MASTER_REFERENCE_ID FROM EMRSCREENIMMUNLKUP
WHERE GROUP_ID = 0 AND MASTER_REFERENCE_ID IS NULL
ORDER BY SCREEN_IMMUN_ID
BEGIN
OPEN @ezEMRxScreenImmunRecords
FETCH NEXT FROM @ezEMRxScreenImmunRecords INTO @SCREEN_IMMUN_ID, @RECORD_TYPE,@GENDER,@SUB_GENDER,@DESCRIPTION,@STARTING_AGE_OPERATOR,
@STARTING_AGE_VALUE,@STARTING_AGE_UNITS,@FREQUENCY,@STARTING_AGE_LIMIT_RANGE,@STARTING_AGE_LIMIT_UNITS,
@AGE_END_LIMIT,@AGE_END_LIMIT_UNITS,@REPEAT,@REPEAT_AGE_VALUE,@REPEAT_AGE_UNITS,@FREQUENCY_LIMIT,
@REPEAT_CONDITION, @STATUS,@CDC_CODE,@SCREEN_COMMENTS,@MASTER_REFERENCE_ID
WHILE @@FETCH_STATUS >= 0
BEGIN
SET @MASTER_REFERENCE_ID = @SCREEN_IMMUN_ID
SET @SCREEN_IMMUN_ID = @currentScreenImmunID
INSERT INTO EMRSCREENIMMUNLKUP (SCREEN_IMMUN_ID, RECORD_TYPE,GENDER,SUB_GENDER,DESCRIPTION,STARTING_AGE_OPERATOR,
STARTING_AGE_VALUE,STARTING_AGE_UNITS,FREQUENCY,STARTING_AGE_LIMIT_RANGE,STARTING_AGE_LIMIT_UNITS,
AGE_END_LIMIT,AGE_END_LIMIT_UNITS,REPEAT,REPEAT_AGE_VALUE,REPEAT_AGE_UNITS,FREQUENCY_LIMIT,
REPEAT_CONDITION, GROUP_ID,STATUS,CDC_CODE,SCREEN_COMMENTS,MASTER_REFERENCE_ID)
SELECT @currentScreenImmunID, @RECORD_TYPE,@GENDER,@SUB_GENDER,@DESCRIPTION,@STARTING_AGE_OPERATOR,
@STARTING_AGE_VALUE,@STARTING_AGE_UNITS,@FREQUENCY,@STARTING_AGE_LIMIT_RANGE,@STARTING_AGE_LIMIT_UNITS,
@AGE_END_LIMIT,@AGE_END_LIMIT_UNITS,@REPEAT,@REPEAT_AGE_VALUE,@REPEAT_AGE_UNITS,@FREQUENCY_LIMIT,
@REPEAT_CONDITION, @GROUP_ID,1,@CDC_CODE,@SCREEN_COMMENTS, @SCREEN_IMMUN_ID

SET @currentScreenImmunID = @currentScreenImmunID + 1
FETCH NEXT FROM @ezEMRxScreenImmunRecords INTO @currentScreenImmunID, @RECORD_TYPE,@GENDER,@SUB_GENDER,@DESCRIPTION,@STARTING_AGE_OPERATOR,
@STARTING_AGE_VALUE,@STARTING_AGE_UNITS,@FREQUENCY,@STARTING_AGE_LIMIT_RANGE,@STARTING_AGE_LIMIT_UNITS,
@AGE_END_LIMIT,@AGE_END_LIMIT_UNITS,@REPEAT,@REPEAT_AGE_VALUE,@REPEAT_AGE_UNITS,@FREQUENCY_LIMIT,
@REPEAT_CONDITION, @STATUS,@CDC_CODE,@SCREEN_COMMENTS,@SCREEN_IMMUN_ID
END
CLOSE @ezEMRxScreenImmunRecords
END
DEALLOCATE @ezEMRxScreenImmunRecords
SET @updateQuery1 = 'UPDATE EMRIDS SET EZEMRXID = ' + Cast(@currentScreenImmunID as VARCHAR) + ' WHERE PROPERTY_NAME = ''' + @propertyName + ''''
EXEC (@updateQuery1)
END
END
[/code]

Hi above one is my trigger and is failing at line no 60 like
Msg 2627, Level 14, State 1, Procedure TR_INSERT_EMRGROUPMASTER, Line 60
INSERT INTO EMRSCREENIMMUNLKUP

Violation of PRIMARY KEY constraint 'EMRScreenImmunLkup_PK'. Cannot insert duplicate key in object 'dbo.EMRSCREENIMMUNLKUP'.

and i have records from screen_immun_id from 1 to 68 and when i drop primary key again inserting from 2 to 68 and giving one row as 2500.

please help in this.i want primary key to be there and values from 2500 onwards for 68 rows

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 08:33:56

Can you explain what you're trying to do with trigger above?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-26 : 08:35:22

visakh i have done it.my trigger works like this


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TR_Insert_EMRGroupMaster]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TR_Insert_EMRGroupMaster]
GO

CREATE TRIGGER TR_Insert_EMRGroupMaster ON  EMRGroupMaster FOR INSERT
AS
BEGIN
	DECLARE @GROUP_ID	NUMERIC(20, 0)
	DECLARE @SI_ID		NUMERIC(20, 0)
	SET @GROUP_ID = (SELECT GROUP_ID FROM INSERTED)
	SET @SI_ID = (SELECT ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME = 'SCREEN_IMMUN_ID')	
	INSERT INTO EMRScreenImmunLkup (SCREEN_IMMUN_ID, RECORD_TYPE, GENDER, SUB_GENDER, 
	[DESCRIPTION], STARTING_AGE_OPERATOR, STARTING_AGE_VALUE, STARTING_AGE_UNITS, 
	FREQUENCY, STARTING_AGE_LIMIT_RANGE, STARTING_AGE_LIMIT_UNITS, AGE_END_LIMIT, 
	AGE_END_LIMIT_UNITS, [REPEAT], REPEAT_AGE_VALUE, REPEAT_AGE_UNITS, FREQUENCY_LIMIT, 
	REPEAT_CONDITION, GROUP_ID, [STATUS], CDC_CODE,SCREEN_COMMENTS, MASTER_REFERENCE_ID) 
	SELECT @SI_ID + SCREEN_IMMUN_ID AS SCREEN_IMMUN_ID, RECORD_TYPE, GENDER, SUB_GENDER, 
	[DESCRIPTION], STARTING_AGE_OPERATOR, STARTING_AGE_VALUE, STARTING_AGE_UNITS, FREQUENCY, 
	STARTING_AGE_LIMIT_RANGE, STARTING_AGE_LIMIT_UNITS, AGE_END_LIMIT, AGE_END_LIMIT_UNITS, 
	[REPEAT], REPEAT_AGE_VALUE, REPEAT_AGE_UNITS, FREQUENCY_LIMIT, REPEAT_CONDITION, 
	@GROUP_ID AS GROUP_ID, [STATUS], CDC_CODE, SCREEN_COMMENTS, 
	SCREEN_IMMUN_ID AS MASTER_REFERENCE_ID 
	FROM EMRScreenImmunLkup WHERE GROUP_ID = 0 AND MASTER_REFERENCE_ID IS NULL
	ORDER BY SCREEN_IMMUN_ID ASC
	UPDATE EMRIDS SET EZEMRXID = (@SI_ID+68) WHERE PROPERTY_NAME = 'SCREEN_IMMUN_ID'
END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 08:40:09

its a real mess. I dont think it would be possible for anybody to understand what it does. At least learn to format code while posting next time!
Your code doesnt make much sense. you're not using INSERTED or DELETED table in main query (you're just taking GROUP_ID and not using value there after??) so not sure what you're trying here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-02-26 : 08:52:56

As soon as I see a post by rajasekar I dont even dare to look at it.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 09:00:33

quote:
Originally posted by Idera

As soon as I see a post by rajasekar I dont even dare to look at it.

PBUH

Seems like I need to follow that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 01:13:57

quote:
Originally posted by Idera

As soon as I see a post by rajasekar I dont even dare to look at it.

PBUH

Thats better. He never responded as soon as he got what he wanted
I have never seen anyone like him

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-01 : 10:37:56

quote:
Originally posted by madhivanan

quote:
Originally posted by Idera

As soon as I see a post by rajasekar I dont even dare to look at it.

PBUH

Thats better. He never responded as soon as he got what he wanted
I have never seen anyone like him

Madhivanan

Failing to plan is Planning to fail

I havent either

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 01:33:24

See still he didn't come back to post here

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 08:50:03

quote:
Originally posted by madhivanan

See still he didn't come back to post here

Madhivanan

Failing to plan is Planning to fail

He wont at least after seeing the reponse of others

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources