Suggestions on instead of inserts
-
Hi Guys, i have a three views that views one table. I have an instead of insert trigger on all three of them, as well as instead of update and instead of delete. Is there anyway to speed up the insert, update, and delete of these views? obviously you have to insert it using a cursor... any ideas?
-
Hi,
I think a little more information is required. Could you give us an idea of what you are trying to accomplish exactly and why you are using your current approach.
obviously you have to insert it using a cursor...I think you may have a change of opinion on this statement before this post runs out.......
Have FunSteve
We need men who can dream of things that never were.
-
ok, my application dynamically builds the views over one big table. i cannot change this, because, everytime i recompile i will loose all data if i don't use views. 2. a trigger only fires off once in a bulk insert therefor the cursor is needed to insert every record. The problem is, this takes so long, even delete from it. I have a unique id for each view in the table, so i can delete from the table, but this is becoming a problem, so i'm wandering if there is a way of speeding up the instead of triggers?
-
If the trigger is only firing once, the problem should really lie in the code / cursor used to perform the Insert / Update / Delete.
Any chance of posting the code used in one of the triggers?
I'm pretty certain one of the real 'set based' thinking guy's will clear this up pretty quickly.....
Confidence, that is.......
Steve
We need men who can dream of things that never were.
-
don't know if i'm right, a trigger only works one line at a time? that's why i use the cursor to insert each record seperatly so that the trigger fires off on each insert. Everything works, but it just takes long.
still confused?
-
Not confused at all. The trigger gives you access the to inserted and deleted tables. Those tables contain the information on all the lines that have been modified.
Inserted : Contains the new data
Deleted : old data
When you do an update, both Inserted (new data) and Deleted (old) are filled.
Here's an exemple of my triggers
CREATE TRIGGER [tr_Temps_Tech_repas_IU] ON [dbo].[_Temps_tech_repas]
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
IF EXISTS (
SELECT
*
FROM Inserted I INNER JOIN dbo.[Bon de travail] BT ON I.[N° bon de travail] = BT.[N° bon de travail]
WHERE I.Simple1 = 0 and I.Demi1 = 0 and I.Double1 = 0 AND BT.Forme 4
)
BEGIN
RAISERROR ('Le type de temps des techniciens (simple, demi ou double) doit être coché si le bon de travail n''est pas un test d''eau (tr_Temps_Tech_repas_IU)', 13, 1)
ROLLBACK TRANSACTION
END
ELSE IF UPDATE(TFin_Char) OR UPDATE(TDebut_Char)
BEGIN
UPDATETTR
SET
TTR.[T début1] = dbo.fnCastTimeAsDecimal (I.TDebut_Char)
, TTR.[T fin1] = dbo.fnCastTimeAsDecimal (I.TFin_Char)
, TTR.stot1 = dbo.fnCastTimeAsDecimal (I.stot_Char)
, TTR.tot1 = dbo.fnCastTimeAsDecimal (I.tot_Char)
FROMdbo._temps_tech_repas TTR INNER JOIN
Inserted I ON TTR.PK = I.PK INNER JOIN
Deleted D ON I.PK = D.PK AND (ISNULL(I.TFin_Char, -1) ISNULL(D.TFin_Char, -1) OR ISNULL(I.TDebut_Char, -1) ISNULL(D.TDebut_Char, -1))
END
As you can see in the final update statement, I join to the inserted and deleted tables so that I update only that lines that have been modified by the update statement, I also validate that any data has been modified by comparing the old and new values from inserted and deleted. This allows to update as little rows as possible. You will also notice this little functions : UPDATE(TFin_Char). This checks that the Column 'TFin_Char' has been included in the update statement (which doesn't mean that any data has been modified. The data could be updated to it's old value; that's why I add another check later on).
Using a trigger like this allows you to update all the lines you need in a single statement, therefore allowing you not to use a cursor and boosting performance by I huge margin.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply