Trigger or Insert issue: Not generating the correct data
-
HI Guys
I have an issue, it is either with my trigger or when i simply insert a row into a table.
Basically my trigger consists of a simple insert from one table to another.
When I insert a row into table1, it should automatically generate that row of data into table2.
That works fine however:
insert into call_list (Row_Number, TelephoneNumber, Balance, CustomerName, AccountReference, SourceSystem)
values (11, 07856783211, 90.00, 'Alistair Cook', 'C33321', 't')
Table1 = call_list - I expect all charaters of the values to be inserted.
ROW_NUMBER BALANCE CUSTOMERNAME ROW_FAILURE ACCOUNTS1 ACCOUNTREFERENCE SOURCESYSTEM TELEPHONE NUMBER
1 300.00 Peter Dan SmithNULL NULL Smith2011NULL t NULL
Once the above row insert into table 2 (out__calllist, it only shows 1 character in the CustomerName, and 1 character in AccountReference, and does NOT show the Telephone Number: as follows:
ROW_NUMBER BALANCE CUSTOMERNAME ROW_FAILURE ACCOUNTS1 ACCOUNTREFERENCE SOURCESYSTEM TELEPHONE NUMBER
1 300.00 P NULL NULL S t NULL
The ones in bold highlight that it does not show the full characters. i.e. PETER DAN SMITH only shows P.
CREATE TRIGGER out_dialler_calllist ON call_list
AFTER INSERT, update AS
Declare
@AccountNumberint,
@CustomerNumber int,
@WorklistIDint,
@DiallerDatedatetime,
@AccountSeqNumberint,
@DateofRequestdatetime,
@AccountReferencenvarchar,
@TelephoneNumberbigint,
@Balancedecimal,
@sourcesystemvarchar,
@ProcessdateDatetime,
@statuscodenvarchar,
@statusreasonnvarchar,
@CustomerNamevarchar
SET@DiallerDate =GETDATE()
BEGIN
SELECT @AccountReference=AccountReference, @TelephoneNumber=TelephoneNumber, @Balance=Balance, @CustomerName=CustomerName
FROM INSERTED
WHERE @DiallerDate IS NOT NULL
INSERT INTO out__calllist (DiallerDate, AccountReference, TelephoneNumber, Balance, CustomerName)
VALUES(@DiallerDate, @AccountReference, @TelephoneNumber, @Balance, @CustomerName)
END
GO
Above is my trigger...anyone have an idea of what might be wrong?
Really hope the above makes sense and appreciate any help
Thanks
Ram
-
if you declare a varchar/char/nvarchar with out a size, it is a single character.
only in a cast/convert is it 30 chars without the size declaration.
declare @var varchar
declare @var2 varchar(50)
besides that, your trigger will only handle one row at a time.
the trigger should be like this instead:
CREATE TRIGGER out_dialler_calllist ON call_listAFTER INSERT, update ASBEGININSERT INTO out__calllist (DiallerDate, AccountReference, TelephoneNumber, Balance, CustomerName)SELECT getdate(),AccountReference, TelephoneNumber, Balance,CustomerNameFROM INSERTEDENDGOLowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi
Thank you for that, it works perfectly.
However, the TelephoneNumber field still does not appear when the trigger fires into out__calllist.
-
ram_kamboz2k7 (2/28/2012)
HiThank you for that, it works perfectly.
However, the TelephoneNumber field still does not appear when the trigger fires into out__calllist.
wierd; how is the data being inserted into the out_dialler_calllist table? could that process be suffering from the same varchar without a size issue?
the destination table, what size is the field there?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi
It is ok i got it to work.
I did not ensure that both tables which have the column TelephoneNumber are set to data type BigInt.
One was set to Int. But that has been changed.
Thanks very much!:-D
-
You still haven't fixed the FATAL FLAW that you were warned about in this thread http://www.sqlservercentral.com/Forums/FindPost1256724.aspx.
Someone (SQL KIWI maybe) mentioned that one company had gone out of business, because they failed to account for inserting multiple records at once.
Drew
Edited: to correct the URL.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Telephone Number BigInt??????
Really?????
I would use varchar(30) and a proper CHECK constraint, instead.
-- Gianluca Sartori
-
if bigint is an issue, can nvarchar be used for the telephone number?
Thanks
-
How do you mean? Currently ev erytime an account is inserted into the call_list table, it automatically generates and that row is inserted into out_calllist table.
Is there an issue with that? The reason I ask is because your pasted link does not work.
-
ram_kamboz2k7 (2/28/2012)
if bigint is an issue, can nvarchar be used for the telephone number?Thanks
Yes, usually people would use character data type when storing telephone numbers. Howevere, as J. Drew Allen rightly pointed out, that whould be the least of your issues with this trigger. Basically, your implementation completely wrong and must not be used within a trigger. You should not select values from INSERTED to variables, as you may have multiple rows in the INSERTED in a single trigger execution/transaction. You will not even know from which row the variables will be populated.
-
ram_kamboz2k7 (2/28/2012)
@DrewHow do you mean? Currently ev erytime an account is inserted into the call_list table, it automatically generates and that row is inserted into out_calllist table.
Is there an issue with that? The reason I ask is because your pasted link does not work.
What about if you will need to do a data-fix and insert multiple records at once?
Something like:
INSERT INTO call_list
SELECT .....
FROM ....
Do you think your trigger will run for each inserted row?
If so you are wrong! Trigger will be executed once per INSERT statement, no matter of how many rows will be inserted at once!
-
Right i understand, I will take that into consideraton.
Thanks for that:-)
-
I totally understand what you mean, I will probably alter my trigger and return if they're any issues.
Many Thanks
-
You are welcome!
Please post your final one, so you get free code review :hehe:
-
ram_kamboz2k7 (2/28/2012)
if bigint is an issue, can nvarchar be used for the telephone number?Thanks
varchar would be a better choice. I highly doubt you will ever need to store unicode characters in telephone numbers.
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply