By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,983 Members | 1,582 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,983 IT Pros & Developers. It's quick & easy.

Instead of INSERT trigger!!

P: 2
Hi,

I am almost going crazy with this. I have a table that I bulk insert into from another database using VS2005.

I need to change the data in the records before committing the values in the fields. To do that I created an (Instead of Insert) trigger.

The trigger basically check conditions using IF statements and accordingly does the appropriate action, which is simply switching values between two fields (swapping).

My problem is that when the trigger fires, the logic within the if statements seem to overlap or something. The whole table fields are swapped rather than only the targeted ones.

Please tell me what is the proper syntax for having multiple (IF THEN ELSE) statements within a trigger.

Here is the code to be more specific:





Expand|Select|Wrap|Line Numbers
  1. set ANSI_NULLS ON
  2. set QUOTED_IDENTIFIER ON
  3. GO
  4. ALTER TRIGGER [dbo].[dbo_fwdcalls]
  5. ON [dbo].[calls]
  6.  
  7. INSTEAD OF INSERT
  8.  
  9. AS
  10.  
  11. BEGIN
  12. SET NOCOUNT ON;
  13. IF EXISTS (SELECT joinonbehalfof FROM INSERTED
  14. WHERE
  15. ((joinonbehalfof='10')
  16. AND (duration > 0)
  17. AND(LEFT(callingpartynumber,1)='8' OR LEFT(callingpartynumber,1)='9' )
  18. AND (lastredirectdn BETWEEN '4020' AND '4024' OR lastredirectdn BETWEEN '5020' AND '5024')
  19. ))
  20.  
  21.  
  22.  
  23. INSERT INTO calls
  24. SELECT [dateTimeOrigination]
  25. ,[origNodeId]
  26. ,[origSpan]
  27. ,[origIpAddr]
  28. ,[finalCalledPartyNumber]--Mark
  29. ,[origCause_location]
  30. ,[origCause_value]
  31. ,[origMediaTransportAddress_IP]
  32. ,[origMediaTransportAddress_Port]
  33. ,[destLegIdentifier]
  34. ,[destNodeId]
  35. ,[destSpan]
  36. ,[destIpAddr]
  37. ,[originalCalledPartyNumber]
  38. ,[callingPartyNumber]--Mark
  39. ,[destCause_location]
  40. ,[destCause_value]
  41. ,[destMediaTransportAddress_IP]
  42. ,[destMediaTransportAddress_Port]
  43. ,[dateTimeConnect]
  44. ,[dateTimeDisconnect]
  45. ,[lastRedirectDn]
  46. ,newid() as Pkid
  47. ,[originalCalledPartyNumberPartition]
  48. ,[finalCalledPartyNumberPartition]--Mark
  49. ,[callingPartyNumberPartition]--Mark
  50. ,[lastRedirectDnPartition]
  51. ,[duration]
  52. ,[origDeviceName]
  53. ,[destDeviceName]
  54. ,[origCalledPartyRedirectReason]
  55. ,[lastRedirectRedirectReason]
  56. ,[destConversationId]
  57. ,[origCallTerminationOnBehalfOf]
  58. ,[destCallTerminationOnBehalfOf]
  59. ,[lastRedirectRedirectOnBehalfOf]
  60. ,[joinOnBehalfOf]
  61. FROM INSERTED
  62.  
  63.  
  64. ELSE
  65.  
  66.  
  67. IF EXISTS
  68. (SELECT joinonbehalfof FROM INSERTED
  69. WHERE
  70. (joinonbehalfof='4')
  71. AND (duration > 0)
  72. AND(LEFT(callingpartynumber,1)='8' OR LEFT(callingpartynumber,1)='9' )
  73. AND NOT (lastRedirectDn BETWEEN '4020' AND '4024' OR lastRedirectDn BETWEEN '5020' AND '5024')
  74. AND (LEFT(lastRedirectDn,1) <> 'b')
  75. )
  76.  
  77.  
  78. INSERT INTO calls
  79. SELECT [dateTimeOrigination]
  80. ,[origNodeId]
  81. ,[origSpan]
  82. ,[origIpAddr]
  83. ,[lastRedirectDn]--Mark
  84. ,[origCause_location]
  85. ,[origCause_value]
  86. ,[origMediaTransportAddress_IP]
  87. ,[origMediaTransportAddress_Port]
  88. ,[destLegIdentifier]
  89. ,[destNodeId]
  90. ,[destSpan]
  91. ,[destIpAddr]
  92. ,[originalCalledPartyNumber]
  93. ,[callingPartyNumber]--Mark
  94. ,[destCause_location]
  95. ,[destCause_value]
  96. ,[destMediaTransportAddress_IP]
  97. ,[destMediaTransportAddress_Port]
  98. ,[dateTimeConnect]
  99. ,[dateTimeDisconnect]
  100. ,[finalCalledPartyNumber]--Mark
  101. ,newid() as Pkid
  102. ,[originalCalledPartyNumberPartition]
  103. ,[callingPartyNumberPartition]
  104. ,[finalCalledPartyNumberPartition]
  105. ,[lastRedirectDnPartition]
  106. ,[duration]
  107. ,[origDeviceName]
  108. ,[destDeviceName]
  109. ,[origCalledPartyRedirectReason]
  110. ,[lastRedirectRedirectReason]
  111. ,[destConversationId]
  112. ,[origCallTerminationOnBehalfOf]
  113. ,[destCallTerminationOnBehalfOf]
  114. ,[lastRedirectRedirectOnBehalfOf]
  115. ,[joinOnBehalfOf]
  116. FROM INSERTED
  117.  
  118. ELSE
  119.  
  120. IF EXISTS
  121. (SELECT joinonbehalfof FROM INSERTED
  122. WHERE
  123. ((joinonbehalfof='10')
  124. AND (duration > 0)
  125. AND(LEFT(callingpartynumber,1)='8' OR LEFT(callingpartynumber,1)='9' )
  126. AND NOT (lastRedirectDn BETWEEN '4020' AND '4024' OR lastRedirectDn BETWEEN '5020' AND '5024')
  127. AND (LEFT(lastRedirectDn,1) <> 'b'))
  128. )
  129.  
  130. INSERT INTO calls
  131. SELECT [dateTimeOrigination]
  132. ,[origNodeId]
  133. ,[origSpan]
  134. ,[origIpAddr]
  135. ,[lastRedirectDn]--Mark
  136. ,[origCause_location]
  137. ,[origCause_value]
  138. ,[origMediaTransportAddress_IP]
  139. ,[origMediaTransportAddress_Port]
  140. ,[destLegIdentifier]
  141. ,[destNodeId]
  142. ,[destSpan]
  143. ,[destIpAddr]
  144. ,[originalCalledPartyNumber]
  145. ,[callingPartyNumber]--Mark
  146. ,[destCause_location]
  147. ,[destCause_value]
  148. ,[destMediaTransportAddress_IP]
  149. ,[destMediaTransportAddress_Port]
  150. ,[dateTimeConnect]
  151. ,[dateTimeDisconnect]
  152. ,[finalCalledPartyNumber]--Mark
  153. ,NEWID() AS Pkid
  154. ,[originalCalledPartyNumberPartition]
  155. ,[callingPartyNumberPartition]
  156. ,[finalCalledPartyNumberPartition]
  157. ,[lastRedirectDnPartition]
  158. ,[duration]
  159. ,[origDeviceName]
  160. ,[destDeviceName]
  161. ,[origCalledPartyRedirectReason]
  162. ,[lastRedirectRedirectReason]
  163. ,[destConversationId]
  164. ,[origCallTerminationOnBehalfOf]
  165. ,[destCallTerminationOnBehalfOf]
  166. ,[lastRedirectRedirectOnBehalfOf]
  167. ,[joinOnBehalfOf]
  168. FROM INSERTED
  169.  
  170.  
  171.  
  172. ELSE
  173.  
  174.  
  175. INSERT INTO calls SELECT * From Inserted
  176.  
  177.  
  178.  
  179.  
  180. END
Jul 28 '07 #1
Share this Question
Share on Google+
2 Replies


P: 39
Disclaimer: Im not an expert.

I wrote an INSTEAD of INSERT trigger a while back and I found my conditions and actions worked just great...

Until I tried to do multi-line inserts that is. Then what was supposed to happen didn't, or happened when it wasn't supposed to. Much like you describe.

The solution I found was to declare the INSERTED table in a cursor thereby handling each record separately. Then my conditions and actions all seemed to work correctly, as they were evaluated and handled independantly.

Example:
In this example I simply wanted to add a surrogate sequential key to my table before insert.

Expand|Select|Wrap|Line Numbers
  1. Create TRIGGER trg_addkey
  2. ON  tblCompositeKey
  3. Instead of INSERT
  4. AS 
  5. Declare @seq int
  6. Declare @key int
  7. Declare @Data char(15)
  8.  
  9. Declare cur_0 scroll cursor for
  10. Select keyfield, data
  11. From inserted
  12.  
  13. open cur_0
  14.  
  15. FETCH NEXT FROM cur_0 into @Key, @Data
  16.  
  17. While @@fetch_status = 0
  18. Begin
  19.  
  20. Set @Seq = (Select isnull((select max(seqfield) from tblCompositeKey Where keyfield = @key),0)+1)
  21.  
  22. Insert into tblCompositeKey
  23. (keyfield, seqfield,data)
  24. Values
  25. (@key,@seq,@Data)
  26.  
  27. FETCH NEXT FROM cur_0 into @Key, @Data
  28. End
  29.  
  30. Close cur_0
  31. Deallocate cur_0
I dont know if that helps at all.
Aug 1 '07 #2

P: 1
You have to encapsulate multi-line IF-ELSE statements in BEGIN/END blocks.

IF (1 = 1)
Single Line Command;
ELSE
Single Line Command;

IF (1 = 1)
BEGIN
Multi-Line Commands;
Multi-Line Commands;
END
ELSE
BEGIN
Multi-Line Commands;
Multi-Line Commands;
END
Aug 2 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.