INSTEAD of INSERT trigger !!!! (Strange Behavior)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

INSTEAD of INSERT trigger !!!! (Strange Behavior)

Author

Topic

leezo
Starting Member

9 Posts

Posted - 2007-07-23 : 15:51:32

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

--------------------------------
-- Conferenced Calls
--------------------------------
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

--------------------------------
-- Employee Transferred Calls
--------------------------------
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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-23 : 15:56:31

put begin ... end in each if block

if ...
begin
...
end
else
begin
...
end

if you don't have them then if uses only the 1st statement

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

leezo
Starting Member

9 Posts

Posted - 2007-07-23 : 16:21:29

Hi Spirit,

Thanks a lot for your help. Another question though, in my INSTEAD OF TRIGGER should I include a ROLLBACK or RETURN statements to really prevent the values to be written to the table or the trigger automatically ONLY commits what is after the IF statement?!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-23 : 16:38:33

well that depends on your use of transactions and business requirements.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

leezo
Starting Member

9 Posts

Posted - 2007-07-23 : 16:51:00

Well, the logic I am after is to only swap the fields where the conditions in the IF statements are true, otherwise I need the rest of the tables to be inserted normally. So, does the trigger syntax provide that override or I have to tell the trigger to it?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources