after insert Triggers problem - 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

General SQL Server Forums

New to SQL Server Programming

after insert Triggers problem

Author

Topic

kancharlasoumya
Starting Member

6 Posts

Posted - 2008-04-03 : 17:31:43

I created a trigger which looks like
Create TRIGGER UpdateGenTables3 ON AWSWQ_Quizzes
After insert as
Begin
SET NOCOUNT ON
declare @TableName varchar(20),@Title varchar(20),@code varchar(20),@Gcode varchar(20),@uppercode varchar(20), @Description varchar(50)
DECLARE DemoCursor CURSOR FOR
Select QuizID,QuizTitle from AWSWQ_Quizzes group by QuizID,QuizTitle
OPEN DemoCursor
FETCH Next From DemoCursor INTO @code,@Title
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO gen_tables (TABLE_NAME, CODE, SUBSTITUTE,UPPER_CODE,DESCRIPTION,OBSOLETE_DESCRIPTION)
VALUES ('PRE_REQ_TESTS', @code, '', @code, @title, '')
FETCH Next From DemoCursor INTO @code,@Title
END
CLOSE DemoCursor
DEALLOCATE DemoCursor

end

The problem with my trigger is .I am not able to insert into my table AWSWQ_Quizzes the error i get when i try to insert is

Violation of PRIMARY KEY constraint 'pkGen_TablesTABLE_NAME'. Cannot insert duplicate key in object 'Gen_Tables'. The statement has been terminated. ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'pkGen_TablesTABLE_NAME'. Cannot insert duplicate key in object 'Gen_Tables'.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-04-03 : 17:45:49

ugh...

First, you are attempting to insert the entire contents of AWSWQ_Quizzes into gen_tables every time the trigger fires. You should read up on TRIGGERs in Books Online and make use of the virtual tables: inserted and deleted.

Next, you should only attempt to insert rows where they don't already exist.

Next, you don't need a cursor.

Here is a quick sample of what I mean: (note that "<PK>" means Primary Key)


Create TRIGGER UpdateGenTables3 ON AWSWQ_Quizzes
After insert as
Begin

SET NOCOUNT ON

INSERT INTO gen_tables (TABLE_NAME, CODE, SUBSTITUTE,UPPER_CODE,DESCRIPTION,OBSOLETE_DESCRIPTION)
select	'PRE_REQ_TESTS', i.quizID, '', i.QuizID, i.QuizTitle, ''
from	inserted i
left join gen_tables gt
		on gt.<PK> = i.<PK of gen_tables>
where	gt.<PK> is null

go

Be One with the Optimizer
TG

kancharlasoumya
Starting Member

6 Posts

Posted - 2008-04-03 : 18:12:07

thank you very much TG.
I removed cursor and i updated my code to

Create TRIGGER UpdateGenTables3 ON AWSWQ_Quizzes
After insert as
Begin
SET NOCOUNT ON

INSERT INTO ibba01imis..gen_tables (TABLE_NAME, CODE, SUBSTITUTE,UPPER_CODE,DESCRIPTION,OBSOLETE_DESCRIPTION)
select 'PRE_REQ_TESTS',AWSWQ_Quizzes.quizID, '', AWSWQ_Quizzes.QuizID, AWSWQ_Quizzes.QuizTitle, ''
from AWSWQ_Quizzes
left join gen_tables gt
on gt.code = cast(AWSWQ_Quizzes.quizID as int) and gt.Table_name='PRE_REQ_TESTS'
where gt.code is null

end

And it worked for me.I removed the cursor and just used insert
thanks
soumya

Soumya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources