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
2 6088
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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jenny |
last post by:
Hi!
I wonder how to use conditions in the inserted table(in a
insert/update) trigger? The inserted table contain all the rows that
have been updated or inserted (for an update/insert trigger),...
|
by: takilroy |
last post by:
Hi,
Does anyone know of a simple way to do this? I want to create an
insert trigger for a table and if the record already exists based on
some criteria, I want to update the table with the...
|
by: Bruce |
last post by:
I have an application written for version 8 that uses an INSTEAD OF
trigger, and I need to convert it to support someone that needs it for
v7. Is there a way to achieve similar functionality in...
|
by: William of Ockham |
last post by:
Hi,
I was asked to recreate a new clean database for our developers because
the current one they use is not entirely up to date. So I created a new
database and I run into the followin strange...
|
by: 73blazer |
last post by:
Perhaps my thinking is wrong but this is what I have:
1 table (Tab1) with 1 attribute (Attr1)
Attr1 char(16) for bit data
-----------------------------------------------
create trigger...
|
by: tomtailor |
last post by:
Hello!
I have a before insert Trigger and I want to catch if there is a
duplicate Key Error. If the Key already exists I'd like to update else
insert the row.
OK I am at the point I did the...
|
by: V T |
last post by:
Hello all,
SQL Server 2000 documentation
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3761.mspx
states that if view is using "NOT NULL" columns of a base table, then...
|
by: teddysnips |
last post by:
I need a trigger (well, I don't *need* one, but it would be optimal!)
but I can't get it to work because it references ntext fields.
Is there any alternative? I could write it in laborious code...
|
by: Maryan |
last post by:
Hi everybody,
i have a view, which contains some columns of two tables. I would like to insert informtions to this view but since this view is created through a join, it is impossible to insert...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |