June 24, 2009 at 8:04 am
Hi All,
I have a trigger -- for insert trigger -- that fires when data is inserted through a third party application.
The application just loads an xml file in the database.
The trigger is used to check for insert, that if there is a case_id for a case feed '7', then take that case_id and append to it a dash(-)101, -102 and so on.
The problem is that when the data is inserted by application, only first 100 records are inserted out of 198 records and the rest 98 records are not inserted with the application error showing "Data access denied".
When I disable the trigger, the records are inserted in the database. So I think that the problem is with the trigger. My guess is that it is causing locking/blocking and so the data access is denied error shows up.
Is there any thing I can do to avoid this, please help.
Thanks,
\\K :w00t:
Trigger code is :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE trigger [dbo].[trig_test_insert]
on [dbo].[cnsmr_accnt]
for insert
AS
BEGIN
declare @seq varchar(25)
set @seq = (select max(substring(cnsmr_id,charindex('-',cnsmr_id)+1,len(cnsmr_id)))+1 from cnsmr_accnt where charindex('-',cnsmr_id) > 0 and case_feed = '7')
If @seq is null
Begin
update cnsmr_accnt
set cnsmr_accnt.cnsmr_id = cnsmr_accnt.cnsmr_id + '-' + '101',
cnsmr_accnt.case_feed = '7'
from cnsmr_accnt
inner join inserted
on cnsmr_accnt.cnsmr_id = inserted.cnsmr_id
WHere cnsmr_accnt.case_feed = '7'
End
Else
Begin
update cnsmr_accnt
set cnsmr_accnt.cnsmr_id = cnsmr_accnt.cnsmr_id + '-' + @seq
from cnsmr_accnt
inner join inserted
on cnsmr_accnt.cnsmr_id= inserted.cnsmr_id
WHere cnsmr_accnt.case_feed = '7'
End
END
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
June 24, 2009 at 9:50 am
Can you post the create table statement please ?
http://www.sqlservercentral.com/articles/Best+Practices/61537/
SQL = Scarcely Qualifies as a Language
June 24, 2009 at 10:18 am
Carl Federl (6/24/2009)
Can you post the create table statement please ?http://www.sqlservercentral.com/articles/Best+Practices/61537/
Hi,
below is the code to create table and some sample data.
CREATE TABLE cnsmr_accnt (
[cnsmr_id] VARCHAR (50) NOT NULL,
[cnsmr_accnt_id] VARCHAR (50) NOT NULL ,
[case_feed] VARCHAR (10) NOT NULL
)
INSERT INTO cnsmr_accnt VALUES ('12', '45683','7')
INSERT INTO cnsmr_accnt VALUES ('13', '45684','7')
INSERT INTO cnsmr_accnt VALUES ('14', '45685','3')
INSERT INTO cnsmr_accnt VALUES ('15', '45686','7')
INSERT INTO cnsmr_accnt VALUES ('16', '45687','2')
INSERT INTO cnsmr_accnt VALUES ('17', '45688','7')
INSERT INTO cnsmr_accnt VALUES ('18', '45689','6')
INSERT INTO cnsmr_accnt VALUES ('19', '45690','7')
INSERT INTO cnsmr_accnt VALUES ('20', '45691','7')
After inserting the data if the new values are inserted for case_feed = 7 then they should be appeneded with a sequence
e.g
INSERT INTO cnsmr_accnt VALUES ('21', '45692','7')
INSERT INTO cnsmr_accnt VALUES ('22', '45693','7')
this should go in as
21-101 45692 7
22-102 45693 7
I can achieve this through trigger and writing insert statements (this is for testing . but when the actual data is loaded through application, it lets me insert first 100 records out of 198 records. and the records inserted are :
21-101-299
22-102-299
I don't know where the -299 comes from, but I believe that as there are 198 records to be inserted, and the sequence is from 101 with the last record as 298, it is taking the 299 value. I may be wrong .. this is my assumption.
If the new records are inserted, it should take the last appended sequence and continue from there for case_feed = 7
Is there any other way to do... like using CTE or other.. if yes please can you guide me or give an example to illustrate....
I am a newbie and programming is not my skill ....
Thanks and sorry for not posting the create statement !!
\\K :hehe:
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
June 24, 2009 at 11:08 am
I tried your code out, then added this insert statement to test out the trigger.
It inserts about 2000 rows into your table, then selects everything.
insert into cnsmr_accnt
select CONVERT(varchar(5), 22 + Number),
CONVERT(varchar(10), 45693 + Number),
'7'
from master.dbo.spt_values where type = 'P'
select * from cnsmr_accnt
I didn't see any issues with adding the extra information onto the end of the cnsmr_id column.
I'm wondering if your application is sending information back that already has the added suffix on it, and that is where it's coming from?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 24, 2009 at 11:19 am
WayneS (6/24/2009)
I tried your code out, then added this insert statement to test out the trigger.It inserts about 2000 rows into your table, then selects everything.
insert into cnsmr_accnt
select CONVERT(varchar(5), 22 + Number),
CONVERT(varchar(10), 45693 + Number),
'7'
from master.dbo.spt_values where type = 'P'
select * from cnsmr_accnt
I didn't see any issues with adding the extra information onto the end of the cnsmr_id column.
I'm wondering if your application is sending information back that already has the added suffix on it, and that is where it's coming from?
Hi Thanks for the quick reply,
I have tested my trigger using the insert statements and it works very good.
BUt when the data is inserted using the application, there where the problem comes.
Note: If the trigger is disabled, then the data gets loaded perfectly OK.
Can any one please help to figure out the problem or suggest workaround to resolve.
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
June 24, 2009 at 12:13 pm
You can try the below code but there may be a bug. If you run the first SQL, the row_number functions gives the expected result but not when included in the trigger.
CREATE TABLE #inserted
( cnsmr_id VARCHAR(40) , case_feed VARCHAR(50))
GO
BEGIN TRAN
INSERT INTO #inserted VALUES ('12' , '7')
INSERT INTO #inserted VALUES ('13' , '7')
INSERT INTO #inserted VALUES ('14' , '7')
INSERT INTO #inserted VALUES ('15' , '7')
SELECTinserted.cnsmr_id + '-'
+ CAST( 100 + ROW_NUMBER() OVER( ORDER BY inserted.cnsmr_id ) AS VARCHAR(4) )
as cnsmr_id_revised
FROM#inserted AS inserted
ROLLBACK
GO
Result is:
cnsmr_id_revised
----------------------
12-101
13-102
14-103
15-104
Trigger code and test case:
ALTER TRIGGER [dbo].[trig_test_insert]
ON [dbo].[cnsmr_accnt]
INSTEAD OF INSERT
AS
SET NOCOUNT ON
IF 0 = (SELECT COUNT(*) FROM inserted ) RETURN
DECLARE @case_feed VARCHAR (10)
SET@case_feed = '7'
-- case_feed not '7' union case_feed is '7'
INSERT INTO dbo.cnsmr_accnt
(cnsmr_id , cnsmr_accnt_id , case_feed )
SELECTinserted.cnsmr_id + '-'
+ CAST( 100 + ROW_NUMBER() OVER( ORDER BY inserted.cnsmr_id ) AS VARCHAR(4) )
,inserted.cnsmr_accnt_id
,inserted.case_feed
FROMinserted
WHEREinserted.case_feed = @case_feed
INSERT INTO dbo.cnsmr_accnt
(cnsmr_id , cnsmr_accnt_id , case_feed )
SELECTinserted.cnsmr_id
,inserted.cnsmr_accnt_id
,inserted.case_feed
FROMinserted
WHEREinserted.case_feed @case_feed
GO
BEGIN TRAN
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('12', '45683','7')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('13', '45684','7')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('14', '45685','3')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('15', '45686','7')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('16', '45687','2')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('17', '45688','7')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('18', '45689','6')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('19', '45690','7')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('20', '45691','7')
SELECT * FROM cnsmr_accnt
ROLLBACK
SQL = Scarcely Qualifies as a Language
June 24, 2009 at 12:54 pm
Hi Carl,
Thanks for your help. You are right that the code when put in trigger will not work.
I cannot understand the use of Instead of trigger as opposed to for/after insert in my code.
My code works fine, but the problem comes when the file is loaded by using the application.
The application gives me "Data access or update failed"
I am still struggling to find a work around.......
Thanks
\\K :hehe:
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
June 24, 2009 at 1:07 pm
the use of Instead of trigger as opposed to for/after insert in my code.
When an insert occurs, the following actions occur and if there is an error in any of the actions, the next action is not run.
1 Instead of trigger is run
2 Constraints are run
3 After trigger is run
When any row fails the constraint check, the after trigger never gets run, hence the "update failed" message. So if there is a duplicate key violation, you do not get a chance to fix the key value unless the fix logic is within a instead of trigger.
SQL = Scarcely Qualifies as a Language
June 24, 2009 at 1:19 pm
SQL_Quest (6/24/2009)
BUt when the data is inserted using the application, there where the problem comes.Note: If the trigger is disabled, then the data gets loaded perfectly OK.
Can any one please help to figure out the problem or suggest workaround to resolve.
Thanks,
\\K
Okay, first you have mentioned two different problems:
1. a "data access error" when inserting from application with trigger enabled.
2. extra characters at the end of the field: 7-105-299 vs 7-105
So, when you say "the problem", it's kinda vague as to which one you mean.
Secondly, there are a couple of people that have posted... we are trying to help you.
So, for the first problem that I listed, can you provide the exact error message that you are getting? You may have to go to the users computer to see it.
For the second problem, can you run a trace and capture exactly what is being sent to the database? I still think that the only way you are getting this is if the application is sending it to you. The trigger does not handle if the code is 7, and the value already has the number appended to it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 24, 2009 at 1:22 pm
Hi Carl,
Thanks for the info. I really need to get on T-SQL to understand and effectively use it.
So if there is a duplicate key violation, you do not get a chance to fix the key value unless the fix logic is within a instead of trigger
The columns which I mentioned has no constraints imposed on them.
Is there a way that my code be modified to use instead of trigger.
can you give some hints so that I can modify my code and come out of the problem.
Still struggling to get rid of this problem ......
Thanks
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
June 24, 2009 at 3:32 pm
For the record, the script Carl posted runs fine and produces the expected output on my 2005 and 2008 servers.
(that was the one saying ROW_NUMBER might not be working in the trigger)
Paul
June 24, 2009 at 3:46 pm
For the record, the script Carl posted runs fine and produces the expected output on my 2005 and 2008 servers.
Paul, thanks for checking.
My newly rebuilt business laptop has NTFS corruption again, so the behavior is a local problem and not related to SQL Server.
SQL = Scarcely Qualifies as a Language
June 24, 2009 at 4:26 pm
So, having read the requirements a bit more thoroughly:Whistling: here's a quick hack which seems to work:
ALTER TRIGGER [dbo].[trig_test_insert]
ON [dbo].[cnsmr_accnt]
INSTEAD OF INSERT
AS
BEGIN
IF@@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
SET ROWCOUNT 0;
;WITHUnioned AS
(
SELECTi.cnsmr_id,
i.cnsmr_accnt_id,
i.case_feed
FROMinserted i
WHEREi.case_feed = '7'
UNIONALL
SELECTLEFT(ca.cnsmr_id, CHARINDEX('-', ca.cnsmr_id) - 1),
ca.cnsmr_accnt_id,
ca.case_feed
FROMdbo.cnsmr_accnt ca
WHEREca.case_feed = '7'
),
Sequenced AS
(
SELECTU.cnsmr_id,
U.cnsmr_id + '-' + CAST(100 + ROW_NUMBER() OVER (ORDER BY U.cnsmr_id) AS VARCHAR(4)) AS new_cnsmr_id,
U.cnsmr_accnt_id,
U.case_feed
FROMUnioned U
)
INSERTdbo.cnsmr_accnt
(cnsmr_id, cnsmr_accnt_id, case_feed)
SELECT S.new_cnsmr_id,
i.cnsmr_accnt_id,
i.case_feed
FROMinserted i
JOINSequenced S
ON(S.cnsmr_id = i.cnsmr_id);
INSERTdbo.cnsmr_accnt
(cnsmr_id, cnsmr_accnt_id, case_feed)
SELECT inserted.cnsmr_id,
inserted.cnsmr_accnt_id,
inserted.case_feed
FROMinserted
WHERE inserted.case_feed '7'
ORinserted.case_feed IS NULL;
END;
GO
BEGIN TRAN
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('12', '45683','7')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('13', '45684','7')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('14', '45685','3')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('15', '45686','7')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('16', '45687','2')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('17', '45688','7')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('18', '45689','6')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('19', '45690','7')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('20', '45691','7')
SELECT * FROM cnsmr_accnt
ROLLBACK
[font="Courier New"]12-101456837
13-102456847
14456853
15-103456867
16456872
17-104456887
18456896
19-105456907
20-106456917
[/font]
I get the feeling that an AFTER trigger might be better suited...?
Paul
June 25, 2009 at 3:12 am
Figured out the problem with the trigger not matching the expected results as this requirement was missing:
When new rows for case_feed = '7' then:
The next sequence should begin after the last appended sequence.
When no row for case_feed = '7' , then first row should start with 101
Previously,the trigger had hard-coded to begin at 100.
ALTER TRIGGER [dbo].[trig_test_insert]
ON [dbo].[cnsmr_accnt]
INSTEAD OF INSERT
AS
SET NOCOUNT ON
IF 0 = (SELECT COUNT(*) FROM inserted ) RETURN
-- case_feed not '7' union case_feed is '7'
INSERT INTO dbo.cnsmr_accnt
(cnsmr_id , cnsmr_accnt_id , case_feed )
SELECT inserted.cnsmr_id + '-'
+ CAST( HighestAssignedSeq + ROW_NUMBER() OVER( ORDER BY inserted.cnsmr_id ) AS VARCHAR(6) )
as cnsmr_id
,inserted.cnsmr_accnt_id
,inserted.case_feed
FROMinserted
CROSS JOIN
/*
When new rows for case_feed = '7' then:
The next sequence should begin after the last appended sequence.
When the first row, then start with 100
*/
(SELECTCOALESCE( CAST ( MAX(SUBSTRING(cnsmr_id,CHARINDEX('-',cnsmr_id)+1,LEN(cnsmr_id))
) AS INTEGER ) , 100 ) As HighestAssignedSeq
FROMdbo.cnsmr_accnt
WHERECHARINDEX('-',cnsmr_id) > 0
ANDcase_feed = '7'
) as case_feed_7_last
WHERE inserted.case_feed = '7'
UNION ALL
SELECT inserted.cnsmr_id
,inserted.cnsmr_accnt_id
, inserted.case_feed
FROMinserted
WHEREinserted.case_feed '7'
go
Test SQL
begin tran
delete from cnsmr_accnt
-- Single row inserts
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed ) VALUES ('12', '45683','7')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed ) VALUES ('13', '45684','7')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed ) VALUES ('14', '45685','3')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed ) VALUES ('15', '45686','7')
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed ) VALUES ('16', '45687','2')
-- Multi row inserts
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed )
SELECT '17', '45688','7' union all
SELECT '18', '45689','6' union all
SELECT '19', '45690','7' union all
SELECT '20', '45691','7'
select *
fromcnsmr_accnt
rollback
Test case results are:
cnsmr_idcnsmr_accnt_idcase_feed
12-101456837
13-102456847
14 456853
15-103456867
16 456872
17-104456887
19-105456907
20-106456917
18 456896
SQL = Scarcely Qualifies as a Language
June 25, 2009 at 3:45 am
...the AFTER trigger version of my code is simpler as expected:
ALTER TRIGGER trg_cnsmr_accnt_ai
ON dbo.cnsmr_accnt
AFTER INSERT
AS
BEGIN
IF@@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
SET ROWCOUNT 0;
;WITHOrdered AS
(
SELECTcnsmr_id,
cnsmr_id + '-' + CONVERT(VARCHAR(10), 100 + ROW_NUMBER() OVER (ORDER BY cnsmr_id ASC)) AS new_value
FROMdbo.cnsmr_accnt
WHEREcase_feed = '7'
)
UPDATEOrdered
SETcnsmr_id = Ordered.new_value
FROMinserted I
JOINOrdered
ON(Ordered.cnsmr_id = I.cnsmr_id)
WHEREI.case_feed = '7';
END;
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply