March 23, 2011 at 12:58 pm
Hello comunity
I build this INSERT TRIGGER to write my customer number on my accounting system when i save my bank deposit, but the problem is when i have 2 or more different customers numbers , and the account is the same 21301.
This is my trigger
ALTER TRIGGER [dbo].[trINSSubcontas] ON
[dbo].[ml]
FOR INSERT
AS
BEGIN
DECLARE @stamp varchar(25), @CONTA VARCHAR(20)
DECLARE @cct varchar(20), @dim1 varchar(20), @dim2 varchar(20), @dim3 varchar(20),
@dim4 varchar(20),@dim5 varchar(20),@SUBCONTA VARCHAR(15), @oristamp varchar(25),
@ORIGEM VARCHAR(6), @DESCRITIVO VARCHAR(60), @EDEB NUMERIC(18,3), @ECRE NUMERIC(18,3),
@CHEQUE VARCHAR(10)
SELECT @stamp = mlstamp, @cct = RTRIM(cct), @dim1 = RTRIM(dim1), @dim2 = RTRIM(dim2),
@dim3 = RTRIM(dim3), @dim4 = RTRIM(dim4),@dim5 = RTRIM(dim5), @SUBCONTA = RTRIM(U_SUBCONTA),
@CONTA = CONTA, @oristamp = oristamp, @ORIGEM = RTRIM(ORIGEM), @DESCRITIVO = RTRIM(DESCRITIVO),
@EDEB = EDEB , @ECRE = ECRE
FROM INSERTED
IF (RTRIM(@SUBCONTA) = '' OR RTRIM(@SUBCONTA) <> '') AND @ORIGEM = 'OX'
AND @conta LIKE '21%'
--I CHECK ALL DIFFERENT CUSTOMERS NUMBERS ON MY TABLE CH (bank check) where exist on the line of my Bank Deposit.
select @SUBCONTA =ch.no from ch (NOLOCK) inner join oy (nolock)
on oy.chstamp = ch.chstamp
inner join ox (nolock) on ox.oxstamp = oy.oxstamp and ox.oxstamp = @oristamp
this query return 2 customer numbers :
14993
15902
--NOW I WANT TO UPDATE THE FIELD : U_SUBCONTA WITH MY CUSTOMER NUMBER
UPDATE ml SET u_subconta = ch.no
FROM (ch (NOLOCK) inner join oy (nolock)
on oy.chstamp = ch.chstamp
inner join ox (nolock) on ox.oxstamp = oy.oxstamp)
INNER JOIN ml on ox.oxstamp = ml.oristamp INNER JOIN INSERTED i ON ml.mlstamp = i.mlstamp
WHERE i.conta <> '' AND i.origem <> 'DO' AND i.ORIGEM = 'OX'
AND i.conta LIKE '21%' and i.ecre <> 0
END
but when i save my bank deposit, on my account line for the same account 21301, i have only on my field subconta the last customer number, like this :
conta(Account) u_subconta debit credit mlstamp oristamp origem
21301 15902 1000 ADM123 OX9999 OX
21301 15902 1000 ADM456 OX9999 OX
12 2000 ADM943 OX9999 OX
I don´t know how to solve this problem, i try different method, but always with the same result.
in practice, the trigger thas note assume when:
the account LIKE '21%' and
MLSTAMP is different but ORISTAMP AND ORIGEM is the same, to paste in the column SUBCONTA the correct customer number return by the query before the UPDATE Statment.
Someone could give my some help
Many thanks
Luis Santos
March 23, 2011 at 3:06 pm
Whenever you start a trigger out by assigning variables, you've lost. Think about your statement
SELECT @stamp = mlstamp, @cct = RTRIM(cct), @dim1 = RTRIM(dim1), @dim2 = RTRIM(dim2),
@dim3 = RTRIM(dim3), @dim4 = RTRIM(dim4),@dim5 = RTRIM(dim5), @SUBCONTA = RTRIM(U_SUBCONTA),
@CONTA = CONTA, @oristamp = oristamp, @ORIGEM = RTRIM(ORIGEM), @DESCRITIVO = RTRIM(DESCRITIVO),
@EDEB = EDEB , @ECRE = ECRE
FROM INSERTED
when more than one row is written to ml. Which inserted.u_subconta will you get?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
March 23, 2011 at 4:08 pm
Hello
Maybe i don´t understand very well your question but i want the (inserted.u_subconta where inserted.u_mlstamp is equal to ml.mlstamp), and as each line has ml.mlstamp (index) different my field ml.u_subconta must be different also, i think
What you suggest to improve my trigger ?
Best regards and thanks.
Luis Santos
March 24, 2011 at 8:22 am
Why can't you get the account# you are trying to insert before you insert into table ml? It seems you have all available info needed at point of insert, but are trying to backtrack after the save. I'm suggesting to not use a trigger and insert the account number with the rest of the row.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
March 25, 2011 at 3:11 pm
This whole trigger is going to cause you problems at some point even if you work out the current issue. The problem is that you are making the (incorrect) assumption that the inserted logical table has only 1 record. This is not always true. As you have noticed when setting a variable it will get set to the last value.
If you provide ddl for the table and clear details about what you are trying this should be pretty straightforward. You will end up with a flexible and maintainable solution.
Check out the link in my signature for details about how to make it easy for us to help you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 25, 2011 at 5:39 pm
Hello Sean
Thanks for your reply.
I attach the script to create table ML and also to insert the records.
on my trigger when i write ,
IF (RTRIM(@SUBCONTA) = '' OR RTRIM(@SUBCONTA) <> '') AND @ORIGEM = 'OX'
AND @conta LIKE '21%'
BEGIN
select @SUBCONTA =ch.no from ch (NOLOCK) inner join oy (nolock)
on oy.chstamp = ch.chstamp
inner join ox (nolock) on ox.oxstamp = oy.oxstamp and ox.oxstamp = @oristamp
is because i need to search my customer number with this SELECT.
i think that my problem is for all lines on ML the field "conta" is the same twice and oristamp is the same for all lines, the unique index that change for ML is MLSTAMP and when i run the UPDATE over my table ML i cannot create a unique key for conta like '21%', because i think when the UPDATE statment start the variable @subconta is empty or maybe MLSTAMP index key that cannot hold.
sorry about my english, but i hope with the script on attach you can see the problem.
Many thanks for your interest to help.
Luis Santos
March 28, 2011 at 7:42 am
Hello Sean
Have you find a solution or ideia for my problem ??
Thanks
Luis Santos
March 28, 2011 at 5:54 pm
Hello comunity
Nobody have an ideia to solve my problem ?
Luis Santos
March 28, 2011 at 7:05 pm
luissantos (3/28/2011)
Hello comunityNobody have an ideia to solve my problem ?
Luis Santos
Many people do not like opening an attached file, such as the zip file you have attached. Who knows what could be in that file. Such things as malicious code, worms, etc.
Click on the first link in my signature block to see how to post table definitions, sample data, etc. The article also contains T-SQL code to assist you in doing so.
March 29, 2011 at 4:16 pm
Hello comunity
I write the code to create table ML and also the lines for 1 document. Also, if i help you , i think the problem is that the update statment return in this case 2 rows, and the Trigger don´t know on which line they must UPDATE.
I read that ORACLE have for TRIGGER the command FOR EACH ROW, but it´s not exist on SQL server.
--create table ML
create table ml
(
mlstamp char (25) constraint DF__ML__MLSTAMP__reconstruido default ('') not null,
dinome varchar (40) constraint DF__ML__DINOME__reconstruido default ('') not null,
dilno numeric (10) constraint DF__ML__DILNO__reconstruido default ((0)) not null,
docnome varchar (40) constraint DF__ML__DOCNOME__reconstruido default ('') not null,
adoc varchar (10) constraint DF__ML__ADOC__reconstruido default ('') not null,
data datetime constraint DF__ML__DATA__reconstruido default (CONVERT([datetime],'19000101',0)) not null,
mes numeric (2) constraint DF__ML__MES__reconstruido default ((0)) not null,
dia numeric (2) constraint DF__ML__DIA__reconstruido default ((0)) not null,
conta varchar (15) constraint DF__ML__CONTA__reconstruido default ('') not null,
descricao varchar (35) constraint DF__ML__DESCRICAO__reconstruido default ('') not null,
rubrica varchar (20) constraint DF__ML__RUBRICA__reconstruido default ('') not null,
deb numeric (18,5) constraint DF__ML__DEB__reconstruido default ((0)) not null,
cre numeric (18,5) constraint DF__ML__CRE__reconstruido default ((0)) not null,
edeb numeric (19,6) constraint DF__ML__EDEB__reconstruido default ((0)) not null,
ecre numeric (19,6) constraint DF__ML__ECRE__reconstruido default ((0)) not null,
dino numeric (4) constraint DF__ML__DINO__reconstruido default ((0)) not null,
descritivo varchar (50) constraint DF__ML__DESCRITIVO__reconstruido default ('') not null,
cct varchar (20) constraint DF__ML__CCT__reconstruido default ('') not null,
debl bit constraint DF__ML__DEBL__reconstruido default ((0)) not null,
vemdedc bit constraint DF__ML__VEMDEDC__reconstruido default ((0)) not null,
recapit varchar (1) constraint DF__ML__RECAPIT__reconstruido default ('') not null,
ncont varchar (18) constraint DF__ML__NCONT__reconstruido default ('') not null,
recapval numeric (18,5) constraint DF__ML__RECAPVAL__reconstruido default ((0)) not null,
docno numeric (6) constraint DF__ML__DOCNO__reconstruido default ((0)) not null,
doctipo numeric (4) constraint DF__ML__DOCTIPO__reconstruido default ((0)) not null,
ordem numeric (10) constraint DF__ML__ORDEM__reconstruido default ((0)) not null,
separa bit constraint DF__ML__SEPARA__reconstruido default ((0)) not null,
dostamp char (25) constraint DF__ML__DOSTAMP__reconstruido default ('') not null,
vemdoext bit constraint DF__ML__VEMDOEXT__reconstruido default ((0)) not null,
erecapval numeric (19,6) constraint DF__ML__ERECAPVAL__reconstruido default ((0)) not null,
lordem numeric (10) constraint DF__ML__LORDEM__reconstruido default ((0)) not null,
npt varchar (20) constraint DF__ML__NPT__reconstruido default ('') not null,
bastamp char (25) constraint DF__ML__BASTAMP__reconstruido default ('') not null,
intid varchar (10) constraint DF__ML__INTID__reconstruido default ('') not null,
idorigem numeric (12) constraint DF__ML__IDORIGEM__reconstruido default ((0)) not null,
origem varchar (4) constraint DF__ML__ORIGEM__reconstruido default ('') not null,
reco bit constraint DF__ML__reco__reconstruido default ((0)) not null,
extracto varchar (12) constraint DF__ML__extracto__reconstruido default ('') not null,
oristamp char (25) constraint DF__ML__oristamp__reconstruido default ('') not null,
olcodigo varchar (12) constraint DF__ML__OLCODIGO__reconstruido default ('') not null,
sgrupo varchar (30) constraint DF__ML__SGRUPO__reconstruido default ('') not null,
grupo varchar (30) constraint DF__ML__GRUPO__reconstruido default ('') not null,
debm numeric (15,2) constraint DF__ML__debm__reconstruido default ((0)) not null,
crem numeric (15,2) constraint DF__ML__crem__reconstruido default ((0)) not null,
pncont varchar (2) constraint DF__ML__PNCONT__reconstruido default ('') not null,
conf1 bit constraint DF__ML__CONF1__reconstruido default ((0)) not null,
conf2 bit constraint DF__ML__CONF2__reconstruido default ((0)) not null,
dim1 varchar (25) constraint DF__ML__DIM1__reconstruido default ('') not null,
dim2 varchar (25) constraint DF__ML__DIM2__reconstruido default ('') not null,
dim3 varchar (25) constraint DF__ML__DIM3__reconstruido default ('') not null,
dim4 varchar (25) constraint DF__ML__DIM4__reconstruido default ('') not null,
dim5 varchar (25) constraint DF__ML__DIM5__reconstruido default ('') not null,
ousrinis varchar (30) constraint DF__ML__OUSRINIS__reconstruido default ('') not null,
ousrdata datetime constraint DF__ML__OUSRDATA__reconstruido default (getdate()) not null,
ousrhora varchar (8) constraint DF__ML__OUSRHORA__reconstruido default ('') not null,
usrinis varchar (30) constraint DF__ML__USRINIS__reconstruido default ('') not null,
usrdata datetime constraint DF__ML__USRDATA__reconstruido default (getdate()) not null,
usrhora varchar (8) constraint DF__ML__USRHORA__reconstruido default ('') not null,
marcada bit constraint DF__ML__MARCADA__reconstruido default ((0)) not null,
u_subconta varchar (10) default ('') not null,
codis varchar (10) default ('') not null,
czonag varchar (1) default ('') not null,
codisconf varchar (10) default ('') not null,
obs text default ('') not null,
chave varchar (1) default ('') not null,
numcontrepres varchar (18) default ('') not null,
codprovincia varchar (2) default ('') not null,
cambio numeric (16,6) default ((0)) not null,
constraint pk_ml primary key nonclustered (mlstamp)
)
go
create nonclustered index in_ml_dostamp
on dbo.ml (dostamp)
go
create nonclustered index in_ml_lordem
on dbo.ml (lordem)
go
create nonclustered index in_ml_data
on dbo.ml (data)
go
create nonclustered index in_ml_mes
on dbo.ml (mes)
go
create nonclustered index in_ml_conta
on dbo.ml (conta)
go
create nonclustered index in_ml_contadata
on dbo.ml (conta,mes,data)
go
create nonclustered index in_ml_ncont
on dbo.ml (ncont)
go
create nonclustered index in_ml_cct
on dbo.ml (cct)
go
create nonclustered index in_ml_bastamp
on dbo.ml (bastamp)
go
--INSERT RECORD ON ML FOR TESTING--------------------------------------------------------------------------
insert into ml (mlstamp, dinome, dilno, docnome, adoc, data, mes, dia, conta, descricao, rubrica, deb, cre, edeb, ecre, dino, descritivo, cct, debl, vemdedc, recapit, ncont, recapval, docno, doctipo, ordem, separa, dostamp, vemdoext, erecapval, lordem, npt, bastamp, intid, idorigem, origem, reco, extracto, oristamp, olcodigo, sgrupo, grupo, debm, crem, pncont, conf1, conf2, dim1, dim2, dim3, dim4, dim5, ousrinis, ousrdata, ousrhora, usrinis, usrdata, usrhora, marcada, u_subconta, codis, czonag, codisconf, obs, chave, numcontrepres, codprovincia, cambio)
values ('ADM11032583551,293000001', 'Titulos a Pagar ', 12000001, 'OP1 - Op.Diversas ', ' ', '2010-12-31', 12, 31, '21301 ', 'Cliente CHPD - MN ', ' ', 0, 22253.502, 0, 111, 1, 'Talão - BES/ / nnnnnnn ', ' ', 0, 0, '', '', 0, 0, 1, 0, 0, 'ADM11032583551,291969479', 1, 0, 1000, ' ', 'ADM11012957448,261328528', ' ', 4, 'OX ', 0, '', 'ADM11012957448,261328528', '', '', '', 0, 0, '', 0, 0, '', '', '', '', '', 'ADM ', '2011-03-25', '23:12:31', 'ADM ', '2011-03-25', '23:12:31', 0, '12563', '', '', '', '', '', '', '', 0)
go
insert into ml (mlstamp, dinome, dilno, docnome, adoc, data, mes, dia, conta, descricao, rubrica, deb, cre, edeb, ecre, dino, descritivo, cct, debl, vemdedc, recapit, ncont, recapval, docno, doctipo, ordem, separa, dostamp, vemdoext, erecapval, lordem, npt, bastamp, intid, idorigem, origem, reco, extracto, oristamp, olcodigo, sgrupo, grupo, debm, crem, pncont, conf1, conf2, dim1, dim2, dim3, dim4, dim5, ousrinis, ousrdata, ousrhora, usrinis, usrdata, usrhora, marcada, u_subconta, codis, czonag, codisconf, obs, chave, numcontrepres, codprovincia, cambio)
values ('ADM11032583551,560000002', 'Titulos a Pagar ', 12000001, 'OP1 - Op.Diversas ', ' ', '2010-12-31', 12, 31, '21301 ', 'Cliente CHPD - MN ', ' ', 0, 1296996.24598, 0, 6469.39, 1, 'Talão - BNU/ / CHQ 12563 ', ' ', 0, 0, '', '', 0, 0, 1, 0, 0, 'ADM11032583551,291969479', 1, 0, 2000, ' ', 'ADM11012957448,261328528', ' ', 4, 'OX ', 0, '', 'ADM11012957448,261328528', '', '', '', 0, 0, '', 0, 0, '', '', '', '', '', 'ADM ', '2011-03-25', '23:12:31', 'ADM ', '2011-03-25', '23:12:31', 0, '12563', '', '', '', '', '', '', '', 0)
go
insert into ml (mlstamp, dinome, dilno, docnome, adoc, data, mes, dia, conta, descricao, rubrica, deb, cre, edeb, ecre, dino, descritivo, cct, debl, vemdedc, recapit, ncont, recapval, docno, doctipo, ordem, separa, dostamp, vemdoext, erecapval, lordem, npt, bastamp, intid, idorigem, origem, reco, extracto, oristamp, olcodigo, sgrupo, grupo, debm, crem, pncont, conf1, conf2, dim1, dim2, dim3, dim4, dim5, ousrinis, ousrdata, ousrhora, usrinis, usrdata, usrhora, marcada, u_subconta, codis, czonag, codisconf, obs, chave, numcontrepres, codprovincia, cambio)
values ('ADM11032583552,664000003', 'Titulos a Pagar ', 12000001, 'OP1 - Op.Diversas ', ' ', '2010-12-31', 12, 31, '1210101 ', 'DO BCP ', ' ', 1319249.74798, 0, 6580.39, 0, 1, 'Talão - B001 DO BCP ', ' ', 0, 0, '', '', 0, 0, 1, 0, 0, 'ADM11032583551,291969479', 1, 0, 3000, ' ', 'ADM11012957448,261328528', ' ', 4, 'OX ', 0, '', 'ADM11012957448,261328528', '', '', '', 0, 0, '', 0, 0, '', '', '', '', '', 'ADM ', '2011-03-25', '23:12:31', 'ADM ', '2011-03-25', '23:12:31', 0, '', '', '', '', '', '', '', '', 0)
go
If you need more information, please let me know.
Best regards
Luis Santos
March 30, 2011 at 9:27 am
First I want to point you to this article[/url] as I think it will help you understand what Todd and Sean are saying. SQL Server triggers work differently than ORACLE triggers and fire once per batch, not per row, so you need to think in sets within the trigger.
Here are some other questions I have:
What is the point of this:
RTRIM(@SUBCONTA) = '' OR
RTRIM(@SUBCONTA) <> ''
I'm not sure what that criteria means. Because the second part of the OR is the inverse of the first part. It looks like you want everything in that column.
Then you do this later:
I.origem <> 'DO' AND
I.ORIGEM = 'OX'
You don't need the first line as the second line limits you to just one value in that column anyway.
I think this trigger below will do the same thing as the one you posted, but will handle multiple rows. As always, test it yourself as I did not test as it seems like a fairly simple problem.
ALTER TRIGGER [dbo].[trINSSubcontas] ON [dbo].[ml]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON ;
/* if there are values that I want to process do something
the IF could be eliminated, but I wanted to keep the same flow
as your original trigger
*/
IF EXISTS ( SELECT
1
FROM
INSERTED
WHERE
(
RTRIM(U_SUBCONTA) = '' OR
RTRIM(U_SUBCONTA) <> ''
) AND
ORIGME = 'OX' AND
CONTA LIKE '21%' )
BEGIN
/* I eliminated athe acces of the oy and oc tables as it looks like
you can join directly from ch to ml based on the join conditions in
your original statements. I also eliminated the NOLOCK hints as they
should not be necessary if you have proper indexing you should get ro
locks which should be released quickly and be lightweight unless you
are inserting a lot of rows that meet the criteria. In the where clause
I eliminated the redundant <> comparisons since you had the = or LIKE
for those columns */
UPDATE
ml
SET
u_subconta = ch.NO
FROM
ml
JOIN INSERTED AS I
ON ml.mlstamp = I.mlstamp
JOIN ch
ON ch.chstamp = ml.oristamp
WHERE
I.ORIGEM = 'OX' AND
I.conta LIKE '21%' AND
I.ecre <> 0
END
END
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2011 at 10:24 am
Hello Jack
First, many thanks for your reply you probably solve my problem but i need to test it.
I have only one dought, suppose that i have 2, 3 or more line with the same conta '213..', how do you know on your script that the first one must have for example the customerID value 14001, the second one CustomerID value 15768,.....
I ask you this question for my curiousity, because when i INSERT on ML the unique index key is MLSTAMP and i must find for each line that is INSERTED who is my CustomerID with conta like '21%'.
Best regards and thanks again.
Luis Santos
March 30, 2011 at 10:37 am
You are going to have to do some translation for me. There is no CustomerID column in the information you provided. Can you translate the cryptic table & column names into something that can be understood?
For instance what is ml? Is ml an order, a customer?
Right now the code I provided will take any inserts into ml and then update the subconta to ch.no for any Conta's beginning with 21 based on the mlstamp = chstamp.
The more I look at what's going on the more I'm wondering why it isn't being handled in the business layer so the original insert has all the data you need.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2011 at 11:41 am
hello Jack
i will keep trying to explain:
You are going to have to do some translation for me. There is no CustomerID column in the information you provided. Can you translate the cryptic table & column names into something that can be understood?
when in refer CustumerID is equal on my table ML to the field u_Subconta, also this values is keep on another table CH , this table keep the banck check number , number of my Customer (ch.no), name of my customer,...etc
For instance what is ml? Is ml an order, a customer?
ML is the table line of my accountancy movment, for example when in this example i insert a bank deposit, i have this movment
u_subconta(CustomerID)
credit account 12(bank account) '' 1000€
debit account 213XXX(Customer account) 14555 200€
debit account 213XXX(Customer account) 17250 100€
debit account 213XXX(Customer account) 12512 700€
Right now the code I provided will take any inserts into ml and then update the subconta to ch.no for any Conta's beginning with 21 based on the mlstamp = chstamp.
The more I look at what's going on the more I'm wondering why it isn't being handled in the business layer so the original insert has all the data you need.
Because , this program was not develop by me, i´am a Implementing Consultant and Reseller.
I hope i could explain better your questions.
Many thanks
Luis Santos
March 30, 2011 at 2:38 pm
This (putting INSERTED/DELETED data into variables inside triggers) is a frighteningly common problem I see at clients, and is a critical data processing flaw. I just went recently to a new client that had ported an Oracle application to SQL Server and they had hundreds of triggers that encapsulated a large portion of the business logic. The company would have ceased to exist had they rolled the new app into production. It isn't just an Oracle port issue though. I wonder how we can get the word out better to keep this from happening.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply