TRIGGER for MULTIPLE ROWS INSERT

[novaphl]

Hi there,<br>I am trying to create a trigger that emulates the autonumber field in Microsoft Access.<br>What I want to do is that every time when I insert a new row(record) in the &quot;holidays&quot; table, the field &quot;id&quot; for the newest row becomes the highest in the table.<br>The code below works great if there is only one row inserted<br><br>&quot;CREATE TRIGGER NOVAPHL.TR_HOLIDAYS_5 AFTER INSERT ON NOVAPHL.HOLIDAYS REFERENCING NEW AS nr FOR EACH ROW MODE DB2SQL WHEN ( ID IS NULL)&nbsp;&nbsp;BEGIN ATOMIC&nbsp;&nbsp;&nbsp;UPDATE HOLIDAYS SET ID = CASE WHEN ((SELECT MAX(ID) FROM HOLIDAYS) IS NULL) THEN 1 ELSE (SELECT MAX(ID) FROM HOLIDAYS) + 1 END WHERE ID IS NULL;&nbsp;&nbsp;END&quot;<br><br>, but if I have more than 1 rows inserted with <br><br>&quot;INSERT INTO holidays ( hdate, description, callcenterid )<br>SELECT masterholidays.hdate, masterholidays.name,22<br>FROM masterholidays WHERE masterholidays.ID IN (1,2)&quot;<br><br>then all the new &quot;id&quot; fields have the same value which is not what I intended to do(I want unique numbers).<br><br><br>Thank you for your help,<br><br>Kyle Tinjala<br>Nova CTI<br><A HREF="mailto:support@novacti.com">support@novacti.com</A>

 

[amosgreg]

I think you are going to have to set up a looping mechanism of some sort and insert each row 1 at a time. (ugly, I know).<br>The select Max function will give you your autonumber but as you founf out the rows are processed as a whole.<br><br>I havn't had time latly to play with this sort of thing but I do remember trying to do something similer in the past and had to do it within a program using similar code.<br><br>Good Luck <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Greg&nbsp;&nbsp; <p>Greg Amos<br><a href=mailto:amosgreg@ix(dot)netcom(dot)com replace(dot)>amosgreg@ix(dot)netcom(dot)com replace(dot)</a><br><a href= > </a><br>

 

[kdsipa]

You have two options:<br><br>1)&nbsp;&nbsp;Upgrade to v7.&nbsp;&nbsp;It has this feature (&quot;Identity&quot; columns).<br><br>2)&nbsp;&nbsp;Write a UDF to do the increment and call it from your trigger.&nbsp;&nbsp;IBM has a white paper on their web site.&nbsp;&nbsp;Sorry I can't point you at the URL.<br>