Instead of Insert Trigger

Question

Instead of Insert Trigger

venod

Grasshopper

Points: 14
More actions
June 10, 2005 at 9:04 am

#88656

Hello,
Can any body pls help me in Instead of Insert Triggers.
The following is the code:
CREATE Trigger Temptrigger
On Temp
Instead of Insert
As
Begin
declare @Id int
declare @TempName varchar(255)
select @TempName = Temp_Name from inserted
select @Id = Temp_Id from inserted

Begin
Insert into Temp1 (Temp1_Id,Temp1_Nm) Values (@Id, @TempName)
End
End
I am trying to insert multirows and its giving me the following error:
Foreign key violation and unable to insert...

Pls help me!!
Thanks

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

First of all, this trigger assume that you have only 1 line in each update.

2nd of all, in the instead of trigger, the data IS NOT inserted in the table. You have to do it manually. Is this your intention?

venod Grasshopper Points: 14 More actions · Answer 2

Thanks for the reply.

My intention is multirow insert .

whenever new row is inserted in Temp table,In temp1 also this should be inserted.

Pls let me know the better way.

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

CREATE Trigger Temptrigger

On Temp

FOR Insert

As

set nocount on

Insert into Temp1 (Temp1_Id,Temp1_Nm) Select Temp_Id, TempName from Inserted

End

venod Grasshopper Points: 14 More actions · Answer 4

Can you pls explain me how it works in case its primary key foreign key relationship.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

What part are you not understanding??

And I'll need to full table definition (with index, keys and constraints)

venod Grasshopper Points: 14 More actions · Answer 6

Temp1 depends on Temp table.

So if there is any duplicate records it will give me foreign key constraints error.

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

no it won't, if you have a constraint that forbids duplicates, this is what will fail, not the foreign key.

The foreign key only makes sure that there's a match in the parent table, nothing more.

venod Grasshopper Points: 14 More actions · Answer 8

Thanks for the solution...

But one more itch is there...

I need to get the maximum count of temp_ID from temp1 table and insert the next temp_id value +1.

Can you pls tell me how do i do that.

Thanks

Vinod

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

why don't you put an identity column instead?it'll be done automatically.

venod Grasshopper Points: 14 More actions · Answer 10

Its not possible to do that since we have already lot of records now and we need to follow the same standard.

Pls help me out in this .

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Well you can create a temp table with identity instead of the current id column, set identity_insert on for that table, ship all your current data to this table, set identity_insert off, drop the old one, rename this new one, recreate the trigger and you're done... for good.