August 15, 2010 at 2:17 am
Hi i am trying to create a trigger but am getting an error and can't seem to fix this.
This is the error
Msg 156, Level 15, State 1, Procedure SITE_UPDATE, Line 21
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Procedure SITE_UPDATE, Line 21
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
and here is the trigger statement
(PS - I tried putting a semi-colon in front of the word "With" in the front of the CTE but got a message saying
" Msg 102, Level 15, State 1, Procedure SITE_UPDATE, Line 22
Incorrect syntax near ';'." )
CREATE TRIGGER [dbo].[SITE_UPDATE] ON [dbo].[CallLog]
FOR INSERT, UPDATE
AS
Declare @CalliD Varchar(8)
Declare @MyVAL varchar(8000)
Select @callid=Callid
FROM inserted WHERE
isnull(inserted.SITES,'') > ''
IF @Callid IS NOT NULL
Begin
-----------------
Select @MyVal=
with cte ("name", "rating") as
(Select Clifton01N as "Name", Clifton01R as "Rating" from inserted where Clifton01Y = 'X' and Clifton01N > '' and callid = @CallID
Union
Select Clifton02N, Clifton02R from inserted where Clifton02Y = 'X' and Clifton02N > ''and callid = @CallID
Union
Select Clifton03N, Clifton03R from inserted where Clifton03Y = 'X' and Clifton03N > ''and callid = @CallID
Union
Select Clifton04N, Clifton04R from inserted where Clifton04Y = 'X' and Clifton04N > ''and callid = @CallID
Union
Select Clifton05N, Clifton05R from inserted where Clifton05Y = 'X' and Clifton05N > ''and callid = @CallID
Union
Select Clifton06N, Clifton06R from inserted where Clifton06Y = 'X' and Clifton06N > ''and callid = @CallID
Union
Select Clifton07N, Clifton07R from inserted where Clifton07Y = 'X' and Clifton07N> ''and callid = @CallID
Union
Select Clifton08N, Clifton08R from inserted where Clifton08Y = 'X' and Clifton08N > ''and callid = @CallID
Union
Select Clifton09N, Clifton09R from inserted where Clifton09Y = 'X' and Clifton09N > ''and callid = @CallID
Union
Select Clifton10N, Clifton10R from inserted where Clifton10Y = 'X' and Clifton10N > ''and callid = @CallID
Union
Select Clifton11N, Clifton11R from inserted where Clifton11Y = 'X' and Clifton11N > ''and callid = @CallID
Union
Select Clifton12N, Clifton12R from inserted where Clifton12Y = 'X' and Clifton12N > ''and callid = @CallID
Union
Select Clifton13N, Clifton13R from inserted where Clifton13Y = 'X' and Clifton13N > ''and callid = @CallID
Union
Select Clifton14N, Clifton14R from inserted where Clifton14Y = 'X' and Clifton14N > ''and callid = @CallID
Union
Select Clifton15N, Clifton15R from inserted where Clifton15Y = 'X' and Clifton15N > ''and callid = @CallID
Union
Select Clifton16N, Clifton16R from inserted where Clifton16Y = 'X' and Clifton16N > ''and callid = @CallID
Union
Select Clifton17N, Clifton17R from inserted where Clifton17Y = 'X' and Clifton17N > ''and callid = @CallID
Union
Select Clifton18N, Clifton18R from inserted where Clifton18Y = 'X' and Clifton18N > ''and callid = @CallID
Union
Select Frenchay01N, Frenchay01R from inserted where Frenchay01Y = 'X' and Frenchay01N > ''and callid = @CallID
Union
Select Frenchay02N, Frenchay02R from inserted where Frenchay02Y = 'X' and Frenchay02N > ''and callid = @CallID
Union
Select Frenchay03N, Frenchay03R from inserted where Frenchay03Y = 'X' and Frenchay03N > ''and callid = @CallID
Union
Select Frenchay04N, Frenchay04R from inserted where Frenchay04Y = 'X' and Frenchay04N > ''and callid = @CallID
Union
Select Frenchay05N, Frenchay05R from inserted where Frenchay05Y = 'X' and Frenchay05N > ''and callid = @CallID
Union
Select Frenchay06N, Frenchay06R from inserted where Frenchay06Y = 'X' and Frenchay06N > ''and callid = @CallID
Union
Select Frenchay07N, Frenchay07R from inserted where Frenchay07Y = 'X' and Frenchay07N > ''and callid = @CallID
Union
Select Frenchay08N, Frenchay08R from inserted where Frenchay08Y = 'X' and Frenchay08N > ''and callid = @CallID
Union
Select Frenchay09N, Frenchay09R from inserted where Frenchay09Y = 'X' and Frenchay09N > ''and callid = @CallID
Union
Select Frenchay10N, Frenchay10R from inserted where Frenchay10Y = 'X' and Frenchay10N > ''and callid = @CallID
Union
Select Frenchay11N, Frenchay11R from inserted where Frenchay11Y = 'X' and Frenchay11N > ''and callid = @CallID
Union
Select Frenchay12N, Frenchay12R from inserted where Frenchay12Y = 'X' and Frenchay12N > ''and callid = @CallID
Union
Select Frenchay13N, Frenchay13R from inserted where Frenchay13Y = 'X' and Frenchay13N > ''and callid = @CallID
Union
Select Frenchay14N, Frenchay14R from inserted where Frenchay14Y = 'X' and Frenchay14N > ''and callid = @CallID
Union
Select Frenchay15N, Frenchay15R from inserted where Frenchay15Y = 'X' and Frenchay15N > ''and callid = @CallID
Union
Select Frenchay16N, Frenchay16R from inserted where Frenchay16Y = 'X' and Frenchay16N > ''and callid = @CallID
Union
Select Frenchay17N, Frenchay17R from inserted where Frenchay17Y = 'X' and Frenchay17N > ''and callid = @CallID
Union
Select Frenchay18N, Frenchay18R from inserted where Frenchay18Y = 'X' and Frenchay18N > ''and callid = @CallID
Union
Select ExeUni01N, ExeUni01R from inserted where ExeUni01Y = 'X' and ExeUni01N > ''and callid = @CallID
Union
Select ExeUni02N, ExeUni02R from inserted where ExeUni02Y = 'X' and ExeUni02N > ''and callid = @CallID
Union
Select ExeUni03N, ExeUni03R from inserted where ExeUni03Y = 'X' and ExeUni03N > ''and callid = @CallID
Union
Select ExeUni04N, ExeUni04R from inserted where ExeUni04Y = 'X' and ExeUni04N > ''and callid = @CallID
Union
Select ExeUni05N, ExeUni05R from inserted where ExeUni05Y = 'X' and ExeUni05N > ''and callid = @CallID
Union
Select ExeUni06N, ExeUni06R from inserted where ExeUni06Y = 'X' and ExeUni06N > ''and callid = @CallID
Union
Select ExeUni07N, ExeUni07R from inserted where ExeUni07Y = 'X' and ExeUni07N > ''and callid = @CallID
Union
Select ExeUni08N, ExeUni08R from inserted where ExeUni08Y = 'X' and ExeUni08N > ''and callid = @CallID
Union
Select ExeUni09N, ExeUni09R from inserted where ExeUni09Y = 'X' and ExeUni09N > ''and callid = @CallID
Union
Select ExeUni10N, ExeUni10R from inserted where ExeUni10Y = 'X' and ExeUni10N > ''and callid = @CallID
Union
Select ExeUni11N, ExeUni11R from inserted where ExeUni11Y = 'X' and ExeUni11N > ''and callid = @CallID
Union
Select ExeUni12N, ExeUni12R from inserted where ExeUni12Y = 'X' and ExeUni12N > ''and callid = @CallID
Union
Select ExeUni13N, ExeUni13R from inserted where ExeUni13Y = 'X' and ExeUni13N > ''and callid = @CallID
Union
Select ExeUni14N, ExeUni14R from inserted where ExeUni14Y = 'X' and ExeUni14N > ''and callid = @CallID
Union
Select ExeUni15N, ExeUni15R from inserted where ExeUni01Y = 'X' and ExeUni15N > ''and callid = @CallID
Union
Select ExeUni16N, ExeUni16R from inserted where ExeUni01Y = 'X' and ExeUni16N > ''and callid = @CallID
Union
Select ExeUni17N, ExeUni17R from inserted where ExeUni01Y = 'X' and ExeUni17N > ''and callid = @CallID
Union
Select ExeUni18N, ExeUni18R from inserted where ExeUni01Y = 'X' and ExeUni18N > ''and callid = @CallID
Union
Select BathCD01N, BathCD01R from inserted where BathCD01Y = 'X' and BathCD01N > ''and callid = @CallID
Union
Select BathCD02N, BathCD02R from inserted where BathCD02Y = 'X' and BathCD02N > ''and callid = @CallID
Union
Select BathCD03N, BathCD03R from inserted where BathCD03Y = 'X' and BathCD03N > ''and callid = @CallID
Union
Select BathCD04N, BathCD04R from inserted where BathCD04Y = 'X' and BathCD04N > ''and callid = @CallID
Union
Select BathCD05N, BathCD05R from inserted where BathCD05Y = 'X' and BathCD05N > ''and callid = @CallID
Union
Select BathCD06N, BathCD06R from inserted where BathCD06Y = 'X' and BathCD06N > ''and callid = @CallID
Union
Select BathJW01N, BathJW01R from inserted where BathJW01Y = 'X' and BathJW01N > ''and callid = @CallID
Union
Select BathJW02N, BathJW02R from inserted where BathJW02Y = 'X' and BathJW02N > ''and callid = @CallID
Union
Select BathJW03N, BathJW03R from inserted where BathJW03Y = 'X' and BathJW03N > ''and callid = @CallID
Union
Select BathJW04N, BathJW04R from inserted where BathJW04Y = 'X' and BathJW04N > ''and callid = @CallID
Union
Select BathJW05N, BathJW05R from inserted where BathJW05Y = 'X' and BathJW05N > ''and callid = @CallID
Union
Select BathJW06N, BathJW06R from inserted where BathJW06Y = 'X' and BathJW06N > ''and callid = @CallID
Union
Select CheltUoG01X, CheltUoG01R from inserted where CheltUoG01 = 'X' and CheltUoG01X > ''and callid = @CallID
Union
Select CheltUoG02X, CheltUoG02R from inserted where CheltUoG02 = 'X' and CheltUoG02X > ''and callid = @CallID
Union
Select CheltUoG03X, CheltUoG03R from inserted where CheltUoG03 = 'X' and CheltUoG03X > ''and callid = @CallID
Union
Select CheltUoG04X, CheltUoG04R from inserted where CheltUoG04 = 'X' and CheltUoG04X > ''and callid = @CallID
Union
Select CheltUoG05X, CheltUoG05R from inserted where CheltUoG05 = 'X' and CheltUoG05X > ''and callid = @CallID
Union
Select CheltUoG06X, CheltUoG06R from inserted where CheltUoG06 = 'X' and CheltUoG06X > ''and callid = @CallID
Union
Select CheltUoG07X, CheltUoG07R from inserted where CheltUoG07 = 'X' and CheltUoG07X > ''and callid = @CallID
Union
Select CheltUoG08X, CheltUoG08R from inserted where CheltUoG08 = 'X' and CheltUoG08X > ''and callid = @CallID
Union
Select CheltUoG09X, CheltUoG09R from inserted where CheltUoG09 = 'X' and CheltUoG09X > ''and callid = @CallID
Union
Select SCAT01N, SCAT01R from inserted where SCAT01Y = 'X' and SCAT01N > ''and callid = @CallID
Union
Select SCAT02N, SCAT02R from inserted where SCAT02Y = 'X' and SCAT02N > ''and callid = @CallID
Union
Select SCAT03N, SCAT03R from inserted where SCAT03Y = 'X' and SCAT03N > ''and callid = @CallID
Union
Select SCAT04N, SCAT04R from inserted where SCAT04Y = 'X' and SCAT04N > ''and callid = @CallID
Union
Select SCAT05N, SCAT05R from inserted where SCAT05Y = 'X' and SCAT05N > ''and callid = @CallID
Union
Select SCAT06N, SCAT06R from inserted where SCAT06Y = 'X' and SCAT06N > ''and callid = @CallID
Union
Select SCAT07N, SCAT07R from inserted where SCAT07Y = 'X' and SCAT07N > ''and callid = @CallID
Union
Select SCAT08N, SCAT08R from inserted where SCAT08Y = 'X' and SCAT08N > ''and callid = @CallID
Union
Select SCAT09N, SCAT09R from inserted where SCAT09Y = 'X' and SCAT09N > ''and callid = @CallID
Union
Select CheltglosCol01N, CheltglosCol01R from inserted where CheltglosCol01Y = 'X' and CheltglosCol01N > ''and callid = @CallID
Union
Select CheltglosCol02N, CheltglosCol02R from inserted where CheltglosCol02Y = 'X' and CheltglosCol02N > ''and callid = @CallID
Union
Select CheltglosCol03N, CheltglosCol03R from inserted where CheltglosCol03Y = 'X' and CheltglosCol03N > ''and callid = @CallID
Union
Select CheltglosCol04N, CheltglosCol04R from inserted where CheltglosCol04Y = 'X' and CheltglosCol04N > ''and callid = @CallID
Union
Select CheltglosCol05N, CheltglosCol05R from inserted where CheltglosCol05Y = 'X' and CheltglosCol05N > ''and callid = @CallID
Union
Select CheltglosCol06N, CheltglosCol06R from inserted where CheltglosCol06Y = 'X' and CheltglosCol06N > ''and callid = @CallID
Union
Select CheltglosCol07N, CheltglosCol07R from inserted where CheltglosCol07Y = 'X' and CheltglosCol07N > ''and callid = @CallID
Union
Select CheltglosCol08N, CheltglosCol08R from inserted where CheltglosCol08Y = 'X' and CheltglosCol08N > ''and callid = @CallID
Union
Select CheltglosCol09N, CheltglosCol09R from inserted where CheltglosCol09Y = 'X' and CheltglosCol09N > ''and callid = @CallID
Union
Select SponsCon01N, SponsCon01R from inserted where SponsCon01Y = 'X' and SponsCon01N > ''and callid = @CallID
Union
Select SponsCon02N, SponsCon02R from inserted where SponsCon02Y = 'X' and SponsCon02N > ''and callid = @CallID
Union
Select SponsCon03N, SponsCon03R from inserted where SponsCon03Y = 'X' and SponsCon03N > ''and callid = @CallID
Union
Select SponsCon04N, SponsCon04R from inserted where SponsCon04Y = 'X' and SponsCon04N > ''and callid = @CallID
Union
Select SponsCon05N, SponsCon05R from inserted where SponsCon05Y = 'X' and SponsCon05N > ''and callid = @CallID
Union
Select SponsCon06N, SponsCon06R from inserted where SponsCon06Y = 'X' and SponsCon06N > ''and callid = @CallID
Union
Select SponsCon07N, SponsCon07R from inserted where SponsCon07Y = 'X' and SponsCon07N > ''and callid = @CallID
Union
Select ClinAcad01N, ClinAcad01R from inserted where ClinAcad01Y = 'X' and ClinAcad01N > ''and callid = @CallID
Union
Select ClinAcad02N, ClinAcad02R from inserted where ClinAcad02Y = 'X' and ClinAcad02N > ''and callid = @CallID
Union
Select ClinAcad03N, ClinAcad03R from inserted where ClinAcad03Y = 'X' and ClinAcad03N > ''and callid = @CallID
Union
Select ClinAcad04N, ClinAcad04R from inserted where ClinAcad04Y = 'X' and ClinAcad04N > ''and callid = @CallID
Union
Select ClinAcad05N, ClinAcad05R from inserted where ClinAcad05Y = 'X' and ClinAcad05N > ''and callid = @CallID
Union
Select ClinAcad06N, ClinAcad06R from inserted where ClinAcad06Y = 'X' and ClinAcad06N > ''and callid = @CallID
Union
Select ClinAcad07N, ClinAcad07R from inserted where ClinAcad07Y = 'X' and ClinAcad07N > ''and callid = @CallID
Union
Select ClinAcad08N, ClinAcad08R from inserted where ClinAcad08Y = 'X' and ClinAcad08N > ''and callid = @CallID
Union
Select ClinAcad09N, ClinAcad09R from inserted where ClinAcad09Y = 'X' and ClinAcad09N > ''and callid = @CallID
Union
Select ExeCol01N, ExeCol01R from inserted where ExeCol01Y = 'X' and ExeCol01N > ''and callid = @CallID
Union
Select ExeCol02N, ExeCol02R from inserted where ExeCol02Y = 'X' and ExeCol02N > ''and callid = @CallID
Union
Select ExeCol03N, ExeCol03R from inserted where ExeCol03Y = 'X' and ExeCol03N > ''and callid = @CallID
Union
Select ExeCol04N, ExeCol04R from inserted where ExeCol04Y = 'X' and ExeCol04N > ''and callid = @CallID
Union
Select ExeCol05N, ExeCol05R from inserted where ExeCol05Y = 'X' and ExeCol05N > ''and callid = @CallID
Union
Select ExeCol06N, ExeCol06R from inserted where ExeCol06Y = 'X' and ExeCol06N > ''and callid = @CallID
Union
Select PlymCity01N, PlymCity01R from inserted where PlymCity01Y = 'X' and PlymCity01N > ''and callid = @CallID
Union
Select PlymCity02N, PlymCity02R from inserted where PlymCity02Y = 'X' and PlymCity02N > ''and callid = @CallID
Union
Select PlymCity03N, PlymCity03R from inserted where PlymCity03Y = 'X' and PlymCity03N > ''and callid = @CallID
Union
Select PlymCity04N, PlymCity04R from inserted where PlymCity04Y = 'X' and PlymCity04N > ''and callid = @CallID
Union
Select PlymCity05N, PlymCity05R from inserted where PlymCity05Y = 'X' and PlymCity05N > ''and callid = @CallID
Union
Select PlymCity06N, PlymCity06R from inserted where PlymCity06Y = 'X' and PlymCity06N > ''and callid = @CallID
Union
Select PlymUni01N, PlymUni01R from inserted where PlymUni01Y = 'X' and PlymUni01N > ''and callid = @CallID
Union
Select PlymUni02N, PlymUni02R from inserted where PlymUni02Y = 'X' and PlymUni02N > ''and callid = @CallID
Union
Select PlymUni03N, PlymUni03R from inserted where PlymUni03Y = 'X' and PlymUni03N > ''and callid = @CallID
Union
Select PlymUni04N, PlymUni04R from inserted where PlymUni04Y = 'X' and PlymUni04N > ''and callid = @CallID
Union
Select PlymUni05N, PlymUni05R from inserted where PlymUni05Y = 'X' and PlymUni05N > ''and callid = @CallID
Union
Select PlymUni06N, PlymUni06R from inserted where PlymUni06Y = 'X' and PlymUni06N > ''and callid = @CallID
Union
Select BridCol01N, BridCol01R from inserted where BridCol01Y = 'X' and BridCol01N > ''and callid = @CallID
Union
Select BridCol02N, BridCol02R from inserted where BridCol02Y = 'X' and BridCol02N > ''and callid = @CallID
Union
Select BridCol03N, BridCol03R from inserted where BridCol03Y = 'X' and BridCol03N > ''and callid = @CallID
Union
Select BridCol04N, BridCol04R from inserted where BridCol04Y = 'X' and BridCol04N > ''and callid = @CallID
Union
Select RAC01N, RAC01R from inserted where RAC01Y = 'X' and RAC01N > ''and callid = @CallID
Union
Select RAC02N, RAC02R from inserted where RAC02Y = 'X' and RAC02N > ''and callid = @CallID
Union
Select RAC03N, RAC03R from inserted where RAC03Y = 'X' and RAC03N > ''and callid = @CallID
Union
Select RAC04N, RAC04R from inserted where RAC04Y = 'X' and RAC04N > ''and callid = @CallID
Union
Select CIRENCESTER01N, CIRENCESTER01R from inserted where CIRENCESTER01Y = 'X' and CIRENCESTER01N > ''and callid = @CallID
Union
Select CIRENCESTER02N, CIRENCESTER02R from inserted where CIRENCESTER02Y = 'X' and CIRENCESTER02N > ''and callid = @CallID
Union
Select CIRENCESTER03N, CIRENCESTER03R from inserted where CIRENCESTER03Y = 'X' and CIRENCESTER03N > ''and callid = @CallID
Union
Select CIRENCESTER04N, CIRENCESTER04R from inserted where CIRENCESTER04Y = 'X' and CIRENCESTER04N > ''and callid = @CallID
Union
Select POP101X, POP101R from inserted where POP101Y = 'X' and POP101X > ''and callid = @CallID
Union
Select POP102X, POP102R from inserted where POP102Y = 'X' and POP102X > ''and callid = @CallID
Union
Select POP201N, POP201R from inserted where POP201Y = 'X' and POP201N > ''and callid = @CallID
Union
Select POP202N, POP202R from inserted where POP202Y = 'X' and POP202N > ''and callid = @CallID
)
Select --cte.*, circuit.circuitname, circuit.popname ,
top 1 (STUFF((SELECT ', ' + [circuitname] FROM circuit FOR XML PATH('')),1,1,''))
from cte
join circuit on cte.name = circuit.circuitdesc
and cte.rating
= circuit.circuitrole
----------------
UPDATE CallLog
set
SITES = @MyVal
Where calllog.CallID = @CallID
END
August 15, 2010 at 4:12 pm
Your assignment statement is in the wrong place. Your statement should be
WITH CTE AS (....)
SELECT @MyVal = ( SELECT .... FROM CTE .... )
Also, the query you are using will produce results that are almost completely independent of your CTE since the subquery for the results are not correlated to the main query where you use the CTE. The only time it will make a difference is when the main query doesn't return any records, in which case, you're probably better off using an IF EXISTS rather than a subquery.
Finally, the WITH keyword can be used in a number of different places. In order to help the parser distinguish between these cases, the WITH keyword used to start a CTE requires that the previous statement end with a semicolon.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 15, 2010 at 11:39 pm
thanks a lot
August 16, 2010 at 3:33 am
Sorry, mate, but placing such code into trigger, is not very good design move.
If you need to use atrigger, you should try making it as lightweight as possible. Systems where business logic is embedded inside of triggers are nightmare from maintenance point of view and usually prone to bad performance on update/insert/delete.
You can make you trigger lightweight by inserting everything from INSERTED into another tables (without any checks) and then run some transformation process (outside of trigger) to transform these data into the required form.
August 16, 2010 at 12:54 pm
Thats interesting. I never thought of that.
So the trigger would just copy the "inserted" data?
August 16, 2010 at 3:40 pm
In looking over your trigger again, I noticed a couple of things. Since you declare your variable as varchar, it only holds the last value when you set the value from the table.
Based solely on the data in your CTE, your table is in desperate need of normalization. You have over 300 fields when it looks like you only need 4. You're using the CTE to normalize the table when you'd be better of normalizing the table itself.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 16, 2010 at 9:47 pm
Thanks Drew
Yeah I know the table design is crap, unfortunately its part of a service desk application that was purchased by the organisation. So theres not really anything that can be done on that side of things
cheers
August 17, 2010 at 4:36 am
iain-656842 (8/16/2010)
Thats interesting. I never thought of that.So the trigger would just copy the "inserted" data?
Actually, to help you more we need a bit more details from you.
Could you please advise how many records you are expecting to be inserted into this table at-once (eg. using INSERT ... SELECT...type of queries).
Will it be one-by-one insert from GUI or another client?
Or you're expecting batch inserts from somewhere else?
What the data you are trying to get in a trigger is going to be used for?
Do you need this data straight away for some thing eles or it will be used for reporting, so it can be formatted a bit later?
August 17, 2010 at 8:34 am
Eugene Elutin (8/17/2010)
Could you please advise how many records you are expecting to be inserted into this table at-once (eg. using INSERT ... SELECT...type of queries).Will it be one-by-one insert from GUI or another client?
Or you're expecting batch inserts from somewhere else?
Since this is for a trigger, you need to be able to handle both. Using a set-based approach, it shouldn't matter, because the approach doesn't care whether the set contains one or multiple items. Using a row-based approach will probably "fail" on any kind of batch. (By "fail", I mean produce the wrong results, not necessarily that it will produce an error.)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 17, 2010 at 9:24 am
drew.allen (8/17/2010)
Eugene Elutin (8/17/2010)
Could you please advise how many records you are expecting to be inserted into this table at-once (eg. using INSERT ... SELECT...type of queries).Will it be one-by-one insert from GUI or another client?
Or you're expecting batch inserts from somewhere else?
Since this is for a trigger, you need to be able to handle both. Using a set-based approach, it shouldn't matter, because the approach doesn't care whether the set contains one or multiple items. Using a row-based approach will probably "fail" on any kind of batch. (By "fail", I mean produce the wrong results, not necessarily that it will produce an error.)
Drew
I have asked the above question for a differnet reason!
Basically, I have questioned the design eg. need of the trigger existence!
For example, if the above task is only required while table is batch loaded, then this logic is better to be part of the batch load itself - not in a trigger at all. By looking into the table desing, it does look more like intermidiate table other than normalised one.
There are no enough information provided by OP here, so it is hard to advice. However from a first glance on a trigger, current design doesn't look very appropriate.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply