Instead of INSERT trigger!!

[leedo]

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:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[dbo_fwdcalls]
ON [dbo].[calls]
 
INSTEAD OF INSERT
 
AS
 
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT joinonbehalfof FROM INSERTED
WHERE
((joinonbehalfof='10')
AND (duration > 0)
AND(LEFT(callingpartynumber,1)='8' OR LEFT(callingpartynumber,1)='9' )
AND (lastredirectdn BETWEEN '4020' AND '4024' OR lastredirectdn BETWEEN '5020' AND '5024')
))
 
 
 
INSERT INTO calls
SELECT [dateTimeOrigination]
,[origNodeId]
,[origSpan]
,[origIpAddr]
,[finalCalledPartyNumber]--Mark
,[origCause_location]
,[origCause_value]
,[origMediaTransportAddress_IP]
,[origMediaTransportAddress_Port]
,[destLegIdentifier]
,[destNodeId]
,[destSpan]
,[destIpAddr]
,[originalCalledPartyNumber]
,[callingPartyNumber]--Mark
,[destCause_location]
,[destCause_value]
,[destMediaTransportAddress_IP]
,[destMediaTransportAddress_Port]
,[dateTimeConnect]
,[dateTimeDisconnect]
,[lastRedirectDn]
,newid() as Pkid
,[originalCalledPartyNumberPartition]
,[finalCalledPartyNumberPartition]--Mark
,[callingPartyNumberPartition]--Mark
,[lastRedirectDnPartition]
,[duration]
,[origDeviceName]
,[destDeviceName]
,[origCalledPartyRedirectReason]
,[lastRedirectRedirectReason]
,[destConversationId]
,[origCallTerminationOnBehalfOf]
,[destCallTerminationOnBehalfOf]
,[lastRedirectRedirectOnBehalfOf]
,[joinOnBehalfOf]
FROM INSERTED
 
 
ELSE
 
 
IF EXISTS
(SELECT joinonbehalfof FROM INSERTED
WHERE
(joinonbehalfof='4')
AND (duration > 0)
AND(LEFT(callingpartynumber,1)='8' OR LEFT(callingpartynumber,1)='9' )
AND NOT (lastRedirectDn BETWEEN '4020' AND '4024' OR lastRedirectDn BETWEEN '5020' AND '5024')
AND (LEFT(lastRedirectDn,1) <> 'b')
)
 
 
INSERT INTO calls
SELECT [dateTimeOrigination]
,[origNodeId]
,[origSpan]
,[origIpAddr]
,[lastRedirectDn]--Mark
,[origCause_location]
,[origCause_value]
,[origMediaTransportAddress_IP]
,[origMediaTransportAddress_Port]
,[destLegIdentifier]
,[destNodeId]
,[destSpan]
,[destIpAddr]
,[originalCalledPartyNumber]
,[callingPartyNumber]--Mark
,[destCause_location]
,[destCause_value]
,[destMediaTransportAddress_IP]
,[destMediaTransportAddress_Port]
,[dateTimeConnect]
,[dateTimeDisconnect]
,[finalCalledPartyNumber]--Mark
,newid() as Pkid
,[originalCalledPartyNumberPartition]
,[callingPartyNumberPartition]
,[finalCalledPartyNumberPartition]
,[lastRedirectDnPartition]
,[duration]
,[origDeviceName]
,[destDeviceName]
,[origCalledPartyRedirectReason]
,[lastRedirectRedirectReason]
,[destConversationId]
,[origCallTerminationOnBehalfOf]
,[destCallTerminationOnBehalfOf]
,[lastRedirectRedirectOnBehalfOf]
,[joinOnBehalfOf]
FROM INSERTED
 
ELSE
 
IF EXISTS
(SELECT joinonbehalfof FROM INSERTED
WHERE
((joinonbehalfof='10')
AND (duration > 0)
AND(LEFT(callingpartynumber,1)='8' OR LEFT(callingpartynumber,1)='9' )
AND NOT (lastRedirectDn BETWEEN '4020' AND '4024' OR lastRedirectDn BETWEEN '5020' AND '5024')
AND (LEFT(lastRedirectDn,1) <> 'b'))
)
 
INSERT INTO calls
SELECT [dateTimeOrigination]
,[origNodeId]
,[origSpan]
,[origIpAddr]
,[lastRedirectDn]--Mark
,[origCause_location]
,[origCause_value]
,[origMediaTransportAddress_IP]
,[origMediaTransportAddress_Port]
,[destLegIdentifier]
,[destNodeId]
,[destSpan]
,[destIpAddr]
,[originalCalledPartyNumber]
,[callingPartyNumber]--Mark
,[destCause_location]
,[destCause_value]
,[destMediaTransportAddress_IP]
,[destMediaTransportAddress_Port]
,[dateTimeConnect]
,[dateTimeDisconnect]
,[finalCalledPartyNumber]--Mark
,NEWID() AS Pkid
,[originalCalledPartyNumberPartition]
,[callingPartyNumberPartition]
,[finalCalledPartyNumberPartition]
,[lastRedirectDnPartition]
,[duration]
,[origDeviceName]
,[destDeviceName]
,[origCalledPartyRedirectReason]
,[lastRedirectRedirectReason]
,[destConversationId]
,[origCallTerminationOnBehalfOf]
,[destCallTerminationOnBehalfOf]
,[lastRedirectRedirectOnBehalfOf]
,[joinOnBehalfOf]
FROM INSERTED
 
 
 
ELSE
 
 
INSERT INTO calls SELECT * From Inserted
 
 
 
 
END

[bwestover]

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.

Create TRIGGER trg_addkey
ON  tblCompositeKey
Instead of INSERT
AS 
Declare @seq int
Declare @key int
Declare @Data char(15)
 
Declare cur_0 scroll cursor for
Select keyfield, data
From inserted
 
open cur_0
 
FETCH NEXT FROM cur_0 into @Key, @Data
 
While @@fetch_status = 0
Begin
 
Set @Seq = (Select isnull((select max(seqfield) from tblCompositeKey Where keyfield = @key),0)+1)
 
Insert into tblCompositeKey
(keyfield, seqfield,data)
Values
(@key,@seq,@Data)
 
FETCH NEXT FROM cur_0 into @Key, @Data
End
 
Close cur_0
Deallocate cur_0

I dont know if that helps at all.

[Irasimus]

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