Solved

microsoft, sql server, 2000, trigger help

Posted on 2008-06-10
18
273 Views
Last Modified: 2010-03-20
folks
In the following trigger I copy the value of the asset.id field to the assetmeter table:

create trigger T_equipment on equipment
for insert as
DECLARE @number int
 SELECT @number = MAX(seed)+1 FROM autokey
insert into measurepoint ( eqnum, description,pointname,pointnum ) ( select eqnum, description,'hours',
 @number +1 from inserted)
update autokey set seed  =(select max(seed)+1 from autokey )

however based on the value of the id i need to do perfom the follows:

if the id field starts with P-100 then I need to now put in 2 inserts ie

insert into measurepoint ( eqnum, description,pointname,pointnum )
  ( select eqnum, description,'hours',@number +1 from inserted)

and

insert into measurepoint ( eqnum, description,pointname,pointnum )
  ( select eqnum, description,'meter',@number +1 from inserted)

if the if the id field starts with w-2000 then I need to now put in 3 inserts ie


insert into measurepoint ( eqnum, description,pointname,pointnum )
  ( select eqnum, description,'moves',@number +1 from inserted)

and

insert into measurepoint ( eqnum, description,pointname,pointnum )
  ( select eqnum, description,'meter',@number +1 from inserted)
and

insert into measurepoint ( eqnum, description,pointname,pointnum )
  ( select eqnum, description,'yards',@number +1 from inserted)

how do i achieve this,note i must also make sure that for each insert the update to the autokey table is also ensured

all help will do
0
Comment
Question by:rutgermons
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
18 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21750476
give this a shot:

create trigger T_equipment on equipment
for insert as
begin
DECLARE @number int
 SELECT @number = MAX(seed)+1 FROM autokey
insert into measurepoint ( eqnum, description,pointname,pointnum ) ( select eqnum, description,'hours',
 @number +1 from inserted)
update autokey set seed  =(select max(seed)+1 from autokey )

--2 inserts

insert into measurepoint ( eqnum, description,pointname,pointnum )
select eqnum, description,'hours',@number +1 from inserted where idfield like 'P-100%'
union all
select eqnum, description,'meter',@number +1 from inserted where idfield like 'P-100%'

--3 inserts

insert into measurepoint ( eqnum, description,pointname,pointnum )
select eqnum, description,'moves',@number +1 from inserted where idfield like 'w-2000%'
union all
select eqnum, description,'moves',@number +1 from inserted where idfield like 'w-2000%'
union all
select eqnum, description,'moves',@number +1 from inserted where idfield like 'w-2000%'

end
0
 

Author Comment

by:rutgermons
ID: 21750514
thanks

will it incrment the autokey table based on every key inserted? ie will this take effetc for each insert?

update autokey set seed  =(select max(seed)+1 from autokey )
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21750636
>>will it incrment the autokey table based on every key inserted? ie will this take effetc for each insert?
No, it will not.  If you need it to do that, you'll need to call your snippet before each insert and take out the UNION to make them all distinct inserts...

 SELECT @number = MAX(seed)+1 FROM autokey
insert into measurepoint ( eqnum, description,pointname,pointnum ) ( select eqnum, description,'hours',
 @number +1 from inserted)
update autokey set seed  =(select max(seed)+1 from autokey )
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:rutgermons
ID: 21750753
thanks

could you provided the full solution pls?

r
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 21750794
sure...try this:

create trigger T_equipment on equipment
for insert as
begin
DECLARE @number int
SELECT @number = MAX(seed)+1 FROM autokey

insert into measurepoint ( eqnum, description,pointname,pointnum ) ( select eqnum, description,'hours',
 @number +1 from inserted)
update autokey set seed  =(select max(seed)+1 from autokey )

--2 inserts

SELECT @number = MAX(seed)+1 FROM autokey
insert into measurepoint ( eqnum, description,pointname,pointnum )
select eqnum, description,'hours',@number +1 from inserted where idfield like 'P-100%'
update autokey set seed  =(select max(seed)+1 from autokey )

SELECT @number = MAX(seed)+1 FROM autokey
insert into measurepoint ( eqnum, description,pointname,pointnum )
select eqnum, description,'meter',@number +1 from inserted where idfield like 'P-100%'
update autokey set seed  =(select max(seed)+1 from autokey )

--3 inserts
SELECT @number = MAX(seed)+1 FROM autokey
insert into measurepoint ( eqnum, description,pointname,pointnum )
select eqnum, description,'moves',@number +1 from inserted where idfield like 'w-2000%'
update autokey set seed  =(select max(seed)+1 from autokey )

SELECT @number = MAX(seed)+1 FROM autokey
insert into measurepoint ( eqnum, description,pointname,pointnum )
select eqnum, description,'moves',@number +1 from inserted where idfield like 'w-2000%'
update autokey set seed  =(select max(seed)+1 from autokey )

SELECT @number = MAX(seed)+1 FROM autokey
insert into measurepoint ( eqnum, description,pointname,pointnum )
select eqnum, description,'moves',@number +1 from inserted where idfield like 'w-2000%'
update autokey set seed  =(select max(seed)+1 from autokey )

end
0
 

Author Comment

by:rutgermons
ID: 21750940
thanks
lemme check
0
 
LVL 22

Expert Comment

by:dportas
ID: 21754084
Note that you will get duplicate numbers in the pointnum column if more than one row is inserted. Also the seed value will increment if the trigger fires even if no row is inserted. Is that what you intended?

Triggers must always be written to perform correctly whether zero, one or many rows are inserted. Otherwise they are an accident waiting to happen.


0
 

Author Comment

by:rutgermons
ID: 21759526
dportas:

how do i get around ur problem,i agree that the given solutions will cause this problem

if u could help i would be very grateful

r
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21759543
Thats always a problem when using a seed table like you're using...you could probably get around it by joining on a UDF and passing in the seed value, but that would put your seed values out of whack because you can't alter data in a function.  Is there anyway you can make your seed column an identity column instead?
0
 

Author Comment

by:rutgermons
ID: 21759662
chapmandew

can we not just build a case in the trigger that first determines the value of the eqnum field and then fires the inserts,if the eqnum does not match this then it should fire inserts in the first place

all help will do
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21759745
But...like dportas said, if you have more than one record inserted your identity values will be off.  The only way around it is to do this in a cursor...which is kinda nasty to do in a trigger.
0
 

Author Comment

by:rutgermons
ID: 21759799
what other options do i have?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21759819
Your best option (IMO) is to create a cursor, loop through the records in the inserted table, and do the inserts that you need, incrementing your seed value after each insert.

DECLARE  CursorTemplate CURSOR
FAST_FORWARD FOR       
      SELECT * FROM inserted

OPEN CursorTemplate

FETCH NEXT FROM CursorTemplate
INTO      @Var1, @Var2, @Var3

WHILE (@@FETCH_STATUS = 0)
BEGIN
      --do your inserts here

      FETCH NEXT FROM CursorTemplate
      INTO      @Var1, @Var2, @Var3

END

CLOSE CursorTemplate
DEALLOCATE CursorTemplate
0
 

Author Comment

by:rutgermons
ID: 21759848
phew

im gonna ask if u can pls help me with the solution,im a bit stumped

tia

r
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21759884
OK...give me an hour or so...working on some other things, and this might take me a couple of minutes.
0
 

Author Comment

by:rutgermons
ID: 21759907
thanks mate,ur saving me some grey hair!!

:)
0
 

Author Comment

by:rutgermons
ID: 21761419
Chapmandrew

i have solved the issue

alter trigger T_eqnumqq on equipment
for insert as
begin
DECLARE @number int


SELECT @number =  MAX(seed)+1 FROM autokey where tbname ='MEASUREPOINT' and name ='POINTNUM'
insert into measurepoint ( eqnum, description,pointname,pointnum,priority,lowerwarning,loweraction,
upperwarning,upperaction,unitofmeasure,siteid,orgid )
(select eqnum, 'Malmaat wissel','MMW010',@number  ,1,1435, 1434,
 1437, 1438,NULL,siteid,orgid from inserted where eqnum like 'W-%')
update autokey set seed  =(select MAX(pointnum) FROM measurepoint )where tbname='MEASUREPOINT'
--Wissel


SELECT @number =  MAX(seed)+1 FROM autokey where tbname ='MEASUREPOINT' and name ='POINTNUM'
and orgid='DEGLOBE'
insert into measurepoint ( eqnum, description,pointname,pointnum,priority,lowerwarning,loweraction,
upperwarning,upperaction,unitofmeasure,siteid,orgid )
(select eqnum, 'Verkanting wissel','VKW011',@number ,1 ,-2,-5, 2,
 5,NULL,siteid,orgid from inserted where eqnum like 'W-%')
update autokey set seed  =(select MAX(pointnum) FROM measurepoint )where tbname='MEASUREPOINT'


SELECT @number =  MAX(seed)+1 FROM autokey where tbname ='MEASUREPOINT' and name ='POINTNUM'
and orgid='DEGLOBE'
insert into measurepoint ( eqnum, description,pointname,pointnum,priority,lowerwarning,loweraction,
upperwarning,upperaction,unitofmeasure,siteid,orgid )
(select eqnum, 'Inlaskop','INLSKP010',@number ,1,15,14,18,25,
NULL,siteid,orgid from inserted where eqnum like 'BO-%')
update autokey set seed  =(select MAX(pointnum) FROM measurepoint )where tbname='MEASUREPOINT'

--3 Bogen
SELECT @number =  MAX(seed)+1 FROM autokey where tbname ='MEASUREPOINT' and name ='POINTNUM'
and orgid='DEGLOBE'
insert into measurepoint ( eqnum, description,pointname,pointnum,priority,lowerwarning,loweraction,
upperwarning,upperaction,unitofmeasure,siteid,orgid )
(select eqnum, 'Kopslijtage Bovenbeen','KPBB010',@number  ,1,10, 9,
 20, 23,NULL,siteid,orgid from inserted where eqnum like 'BO-%')
update autokey set seed  =(select MAX(pointnum) FROM measurepoint )where tbname='MEASUREPOINT'


SELECT @number =  MAX(seed)+1 FROM autokey where tbname ='MEASUREPOINT' and name ='POINTNUM'
and orgid='DEGLOBE'
insert into measurepoint ( eqnum, description,pointname,pointnum,priority,lowerwarning,loweraction,
upperwarning,upperaction,unitofmeasure,siteid,orgid )
(select eqnum, 'Kopslijtage Onderbeen','KPOB020',@number  ,1,10,9,
20,23,NULL,siteid,orgid from inserted where eqnum like 'BO-%')
update autokey set seed  =(select MAX(pointnum) FROM measurepoint )where tbname='MEASUREPOINT'

SELECT @number =  MAX(seed)+1 FROM autokey where tbname ='MEASUREPOINT' and name ='POINTNUM'
and orgid='DEGLOBE'
insert into measurepoint ( eqnum, description,pointname,pointnum,priority,lowerwarning,loweraction,upperwarning,upperaction,unitofmeasure,siteid,orgid )
(select eqnum, 'Slijtage Bovenbeen Boog','SLBB010',@number ,1,-1,-2,
 6,8,NULL,siteid,orgid from inserted where eqnum like 'BO-%')
update autokey set seed  =(select MAX(pointnum) FROM measurepoint )where tbname='MEASUREPOINT'

SELECT @number =  MAX(seed)+1 FROM autokey where tbname ='MEASUREPOINT' and name ='POINTNUM'
and orgid='DEGLOBE'
insert into measurepoint ( eqnum, description,pointname,pointnum,priority,lowerwarning,loweraction,upperwarning,upperaction,unitofmeasure,siteid,orgid )
(select eqnum, 'Zijdelingse slijtage vangrail Bovenbeen','SLVGRLBB010',@number ,1,-1, -2,
 3, 4,NULL,siteid,orgid from inserted where eqnum like 'BO-%')
update autokey set seed  =(select MAX(pointnum) FROM measurepoint )where tbname='MEASUREPOINT'

SELECT @number =  MAX(seed)+1 FROM autokey where tbname ='MEASUREPOINT' and name ='POINTNUM'
and orgid='DEGLOBE'
insert into measurepoint ( eqnum, description,pointname,pointnum,priority,lowerwarning,loweraction,upperwarning,upperaction,unitofmeasure,siteid,orgid )
(select eqnum, 'Zijdelingse slijtage vangrail Onderbeen','SLVGRLOB0202',@number ,1,-1, -2,
 3, 4,NULL,siteid,orgid from inserted where eqnum like 'BO-%')
update autokey set seed  =(select MAX(pointnum) FROM measurepoint )where tbname='MEASUREPOINT'
end

however if u have ur solution i would be keen to see it,i will credit u anyhonw because of u dilligent assistance

r
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21761948
I haven't had a chance to get to it yet, unfortunately....but, it would basically be something like this:

alter trigger T_eqnumqq on equipment
for insert as
begin
DECLARE @number int

DECLARE  CursorTemplate CURSOR
FAST_FORWARD FOR      
      SELECT field1, field2, field3 FROM inserted

OPEN CursorTemplate

FETCH NEXT FROM CursorTemplate
INTO      @Var1, @Var2, @Var3

WHILE (@@FETCH_STATUS = 0)
BEGIN
insert into measurepoint ( eqnum, description,pointname)
select @var1, @var2, @var3

--update your seed table to +1

      FETCH NEXT FROM CursorTemplate
      INTO      @Var1, @Var2, @Var3

END

CLOSE CursorTemplate
DEALLOCATE CursorTemplate

end


However, if yours works then go with it. :)
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
partitioning database after decade growth 8 79
Can I skip a node in XML? 9 46
Need to replicate a Log table 4 40
Database Availability Group Distribution 9 47
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question