June 25, 2009 at 3:59 am
Agree that the SQL for an after trigger appears simplier but try with a schema change and the after trigger.
Alter TABLE [dbo].[cnsmr_accnt]
add constraint cnsmr_accnt_PK primary key ( cnsmr_id )
Then run these this test case:
INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed )
SELECT '17', '45688','7' union all
SELECT '17', '45689','6' union all
SELECT '17', '45690','7' union all
SELECT '17', '45691','7'
Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint 'cnsmr_accnt_PK'. Cannot insert duplicate key in object 'dbo.cnsmr_accnt'.
The statement has been terminated.
SQL = Scarcely Qualifies as a Language
June 25, 2009 at 11:19 am
Paul White (6/25/2009)
...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;
Hi,
The code works OK, but if it is the same cnsmr_id and same case_feed, it inserts without incrementing the sequence.
e.g. cnsmr_id cnsmr_accnt case_feed
14 2345 7
14 2345 7
output is
cnsmr_id cnsmr_accnt case_feed
14-101 2345 7
14-101 2345 7
Expected is
cnsmr_id cnsmr_accnt case_feed
14-101 2345 7
14-102 2345 7
Thanks for your help.
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
June 25, 2009 at 11:28 am
Carl Federl (6/25/2009)
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 trandelete 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
Hi Carl,
I appreciate you efforts in resolving the problem.
I tried your code.
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
here if the column cnsmr_accnt_id is removed along with inserted.cnsmr_accnt_id in your whole code,
it gives me an error message:
Msg 515, Level 16, State 2, Procedure trig_test_insert1, Line 8
Cannot insert the value NULL into column 'cnsmr_accnt_id', table 'test_oltp_rn.dbo.cnsmr_accnt'; column does not allow nulls. INSERT fails.
The statement has been terminated.
The reason, I an eliminating cnsmr_accnt_id is that the application that loads the data in the database, loads into multiple tables, and the table where I want the sequence to be appended, I am checking for just two columns -- cnsmr_id and case_feed. Also there are some computed columns that will automatically get updated based on other columns.
I will try to modify your code to see how it works.
Thanks for all you effort..... trying to resolve the problem :hehe:
I learned a lot and will continue to learn ...
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
June 25, 2009 at 3:11 pm
Carl Federl (6/25/2009)
Agree that the SQL for an after trigger appears simplier but try with a schema change and the after trigger.
Bizarre. So if we change the question the answer changes? 😀
Heh. So given a proper primary key (i.e. a way of identifying a row uniquely) we have a proper table and an AFTER trigger can be written to work easily. My contention is that the AFTER trigger is better suited to this problem than INSTEAD OF.
Paul
June 26, 2009 at 2:51 pm
From Paul White:
Bizarre. So if we change the question the answer changes?
Heh. So given a proper primary key (i.e. a way of identifying a row uniquely) we have a proper table and an AFTER trigger can be written to work easily. My contention is that the AFTER trigger is better suited to this problem than INSTEAD OF.
In general, given a problem and multiple alternative solutions, then choose the simplier.
If the alternative solutions are equally simple, choose the one with the least cost - how do balance development cost and run-time resource costs is an interesting question, which is best left for a different discusssion.
If the alternative solutions are equally simple and costly, choose the one with the fewest dependencies.
As the After or Instead of trigger alternative solutions are both equal in simplicity and cost, but the instead of does not have a dependency on constraints not existing, I would choose the instead of solution.
SQL = Scarcely Qualifies as a Language
June 26, 2009 at 3:01 pm
SQL_Quest:
if the column cnsmr_accnt_id is removed along with inserted.cnsmr_accnt_id in your whole code, it gives me an error message:
Msg 515, Level 16, State 2, Procedure trig_test_insert1, Line 8
Cannot insert the value NULL into column 'cnsmr_accnt_id', table 'test_oltp_rn.dbo.cnsmr_accnt'; column does not allow nulls. INSERT fails.
Yes, that will be true if the column is defined with a NOT NULL constraint and does not have a default constraint. You must specify a value for the column.
The reason, I an eliminating cnsmr_accnt_id is that the application that loads the data in the database, loads into multiple tables, and the table where I want the sequence to be appended, I am checking for just two columns -- cnsmr_id and case_feed.
In an "INSTEAD OF" trigger, the initiating statement is intercepted and if the action is not performed by the trigger, then the database state will not be changed. a simplified example to show this effect is to have an instead of trigger with only a "RETURN" in the body and you will see that the SQL statement has no effect.
SQL = Scarcely Qualifies as a Language
June 26, 2009 at 6:27 pm
Carl Federl (6/25/2009)
Agree that the SQL for an after trigger appears simplier but...
I'll go with the simpler one then 😀
Anyhow, it seems the thread has it's answer and we have also shown that ROW_NUMBER works correctly in triggers which was the other question that came up.
Paul
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply