Create trigger When There's Update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Create trigger When There's Update

Author

Topic

cycheang
Starting Member

40 Posts

Posted - 2011-06-06 : 04:01:43

Hello All,

I'm new to trigger thingy in SQL 2000. Currently i have a task to create a trigger when there is an update/insert/delete performed in one table.

Now, we have 2 table involve for this trigger.
a) material inventory table
b) Rack Id master table'

Trigger is taking place on Material Inventory table. If there is any
insert/update/delete on RackID or quantity it will trigger to add/minus the number of available space in Rack ID table.

If quantity become zero, trigger will minus the quantity in Rack ID table.

I tried to perform update multiple records at one go.. But it only affect last record.
For Instance:
- Update 10 partID from old location to new. Old location in Rack ID table only minus 1 and new location add 1.

There can be multiple of scenario where it could be updating to new Rack ID but the quantity become zero and this wont trigger any.

Anyone expert here do lend me your hand in this.

Many thanks in advance.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-06-06 : 04:26:14

Post the code.

My guess is that the trigger's written assuming there's only ever one row in the inserted/deleted tables.

--
Gail Shaw
SQL Server MVP

cycheang
Starting Member

40 Posts

Posted - 2011-06-06 : 04:49:53

CREATE TRIGGER [UpdRackID] ON [dbo].[MatInventory]
For UPDATE
AS
declare @OldRack as char(10), @NewRack as char(10), @OldQty as float(8), @NewQty as float(8), @PlantCode as char(4), @WhCode as char(2)

Select @PlantCode = a.PlantCode, @WhCode = a.WhseCode, @OldRack = a.RackID, @NewRack= b.RackID, @OldQty = a.Qty, @NewQty= b.Qty
From deleted a, inserted b

if @PlantCode = 'A1' And @WhCode = 'W1'
Begin
set nocount on
if update(RackID) or update(Qty)
Begin
if @OldRack<> @NewRack
Begin
If @OldRack<> '' And @NewRack<> ''
Begin
If @OldQty> 0 And @NewQty= 0
Begin
Update RackID Set AvailSpace = AvailSpace -1 Where Location = @OldRack
end
Else
if @OldQty= 0 And @NewQty> 0
Begin
Update RackID Set AvailSpace = AvailSpace -1 Where Location = @OldRack
Update RackID Set AvailSpace = AvailSpace +1 Where Location = @NewRack
end
else
if @OldQty> 0 And @NewQty> 0
Begin
Update RackID Set AvailSpace = AvailSpace -1 Where Location = @OldRack
Update RackID Set AvailSpace = AvailSpace +1 Where Location = @NewRack
end
end
Else
if @OldRack= '' And @NewRack<> ''
Begin
If @OldQty= 0 And @NewQty> 0
Begin
Update RackID Set AvailSpace = AvailSpace +1 Where Location = @NewRack
end
else
if @OldQty> 0 And @NewQty> 0
Begin
Update RackID Set AvailSpace = AvailSpace +1 Where Location = @NewRack
end
end
Else
If @OldRack<> '' And @NewRack= ''
Begin
If @OldQty> 0 And @NewQty= 0
Begin
Update RackID Set AvailSpace = AvailSpace -1 Where Location = @OldRack
End
Else
If @OldQty> 0 And @NewQty> 0
Begin
Update RackID Set AvailSpace = AvailSpace -1 Where Location = @OldRack
End
End
End
Else
If @OldRack= @NewRack
Begin
If @OldQty= 0 And @NewQty> 0
Begin
Update RackID Set AvailSpace = AvailSpace +1 Where Location = @OldRack
End
Else
If @OldQty> 0 And @NewQty= 0
Begin
Update RackID Set AvailSpace = AvailSpace -1 Where Location = @OldRack
End
End
end
Set Nocount off
End

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-06-06 : 06:41:47

quote:

Select @PlantCode = a.PlantCode, @WhCode = a.WhseCode, @OldRack = a.RackID, @NewRack= b.RackID, @OldQty = a.Qty, @NewQty= b.Qty
From deleted a, inserted b

Yup. That assumes there will only ever be a single row in inserted and deleted. That is not the case. The deleted and inserted tables contain all the rows affected by the update.

You need to rewrite that as a set-based operation, one that will work no matter how many rows are in those tables.

--
Gail Shaw
SQL Server MVP

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-06 : 07:13:46

probably something like this


UPDATE	r
SET	AvailSpace	= r.AvailSpace
			+ CASE 	WHEN r.Location = d.RackID THEN -1
				WHEN r.Location = i.RackID AND i.Qty > 0 THEN +1
				ELSE 0
				END
				
FROM	deleted d
	INNER JOIN inserted i	ON	d.PlantCode	= i.PlantCode	-- JOIN with PK of the table
				AND	d.WhCode	= i.WhCode	
	INNER JOIN RackID r	ON	r.Location	in (d.RackID, i.RackID)
WHERE	d.PlantCode	= 'A1'
and	d.WhCode	= 'W1'
and	(
		d.RackID	<> i.RackID
	or	d.Qty		<> i.Qty
	)

KH
[spoiler]Time is always against us[/spoiler]

cycheang
Starting Member

40 Posts

Posted - 2011-06-06 : 10:29:04

Hello Gilamonster and khtan,

Many thanks for your kindness. Really appreaciate that. Since I'm still new on this trigger, i will need more time to digest and study further. I will try on that once i go back office tomorrow.

Basically, i need the available space for RackID table is to let the program to know before UID is being created, program will know the available space on RackID so that program will assign accordingly.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-06-06 : 10:36:59

Khtan's code should be a good start. Try sorting out the logic as a select of the real tables first, then you can change it to use inserted/deleted. You need to keep in mind that the update in the trigger must be capable of handling multiple rows in the inserted and deleted table.

--
Gail Shaw
SQL Server MVP

cycheang
Starting Member

40 Posts

Posted - 2011-06-06 : 23:44:15

I tried the above example and it only work on single row change. If i perform update the RackID simultaneously on 10 records and it only update 1 at new RackID. Anything should i add in so that it will add 10 in new RackID and -10 in old RackID?

From the case provided above, i need to add in more condition to cater for the requirement.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-06 : 23:51:14

I might have interpret your requirement wrongly.

Let's start over again.

1. post your table structure in DML (create table . . . )
2. sample data in DDL (insert into table . . )
3. expected result

KH
[spoiler]Time is always against us[/spoiler]

cycheang
Starting Member

40 Posts

Posted - 2011-06-07 : 01:59:20

Correction: Previously mentioned column in RackID.AvailSpace and it's been corrected to RackID.NoOfItem

1a) Material Inventory Table (Table name: MatUID)
Column: PlantCode, WhCose, UID, Qty, RackID
Key: PlantCode, WhCode, UID
1b) Rack ID Master Table (Table Name: RackID)
Column: PlantCode, WhCode, Location, NoOfItem
Key: PlantCode, WhCode, Location
Note: Location = MatUID.RackID, PlantCode = MatUID.PlantCode, WhCode = MatUID.WhCode

2) MatUID (PlantCode, WhCose, UID, Qty, RackID)
Data: A1, W1, SW11130P, 2000, QA12
A1, W1, SW11135P, 1000, QA13

RackID (PlantCode, WhCode, Location, NoOfItem)
Data: A1, W1, QA12, 30
A1, W1, QA13, 80

Results: Keep track of number of MatUID in particular RackID. Each part number in warehouse is being assigned an unique UID, we called it as MatUID. Let say it is an electronic components, one reel of UID (SW11130P) consist of qty 2000 and it stay at RackID QA12, it meant that the NoOfItem in table RackID will become 1 after MatUID is created.

Requirement:
1) Trigger only applied on MatUID in PlantCode "A1" And WhCode = "W1" and it will cover the insert/update/delete
2) When there is a update on RackID for MatUID from QA12 to QA13, it will trigger to add 1 in NoOfItem for
QA13 and -1 in QA12
2) Relocation to different Wh, if qty relocation is full (qty become 0) then, it will trigger to -1 in NoOfItem .
3) Trigger to +1 if quantity relocate back from different WhCode

4) Posting will be done in WhCode = "W1". If qty become zero after MatUID posted, then it will trigger to update RackID.NoOfItem -1.
Note: A single posting will cover multiple MatUID in different RackID.
5) Have to cater if 10 MatUID location is being updated from OldRackID to NewRAckID (OldRackID -10 and NewRackID + 10)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-07 : 02:34:49

will creating a VIEW out of MatUID do for you ?


create view RackView
as
select PlantCode, WhCode, Location = RackID, NoOfItem = count(*)
from   MatUID
group by PlantCode, WhCode, RackID

KH
[spoiler]Time is always against us[/spoiler]

cycheang
Starting Member

40 Posts

Posted - 2011-06-07 : 02:53:21

Hello KHTan,

Thanks for your strong support on this. This is the initial plan in my mind. But after tested it out it took more than 3 minutes to get the results.
It is not practical for them to wait more than 3 minutes to wait for the results.

The reason why i need to get the NoOfItem is to let People in warehouse know the quantity of Reel in the designated RackID so that they can choose correct RackID before MatUID is created.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-07 : 04:58:41

[code]
create trigger ti_MatUID on MatUID for INSERT, UPDATE, DELETE
as
begin
-- Create record in RackID if not exists
insert into RackID (PlantCode, WhCode, Location, NoOfItem)
select distinct PlantCode, WhCode, Location = RackID, NoOfItem = 0
from inserted i
where i.PlantCode = 'A1'
and i.WhCode = 'W1'
and not exists
(
select *
from RackID x
where x.PlantCode = i.PlantCode
and x.WhCode = i.WhCode
and x.Location = i.RackID
)

update r
set NoOfItem = r.NoOfItem + i.NoOfItem
from (
select PlantCode, WhCode, RackID, NoOfItem = count(*)
from inserted
group by PlantCode, WhCode, RackID
) i
inner join RackID r on i.PlantCode = r.PlantCode
and i.WhCode = r.WhCode
and i.RackID = r.Location
where i.PlantCode = 'A1'
and i.WhCode = 'W1'

update r
set NoOfItem = r.NoOfItem - d.NoOfItem
from (
select PlantCode, WhCode, RackID, NoOfItem = count(*)
from deleted
group by PlantCode, WhCode, RackID
) d
inner join RackID r on d.PlantCode = r.PlantCode
and d.WhCode = r.WhCode
and d.RackID = r.Location
where d.PlantCode = 'A1'
and d.WhCode = 'W1'
end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

cycheang
Starting Member

40 Posts

Posted - 2011-06-07 : 22:11:34

Hello Khtan,

Thousand thanks for your help. I tried to modified the trigger a bit and it is working. I remove the insert into RackID table if it is not exist because our process is that, without exist of location in RackID table, the RackID wont exist in MatUID table.

Now i need to figure out to put in all the condition in such as quantity consumption finished of UID in particular location, so that is will trigger to minus the NoOfItem in RackID table. Or if there is any insert of record in MatUID table, it will trigger to + noofitem in RackID.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-07 : 22:38:12

quote:
Thousand thanks for your help. I tried to modified the trigger a bit and it is working. I remove the insert into RackID table if it is not exist because our process is that, without exist of location in RackID table, the RackID wont exist in MatUID table.

This is fine. I added that cause i am not sure of your design. I guess you must have a FK constraint of RackID in MatUID

quote:
Now i need to figure out to put in all the condition in such as quantity consumption finished of UID in particular location, so that is will trigger to minus the NoOfItem in RackID table. Or if there is any insert of record in MatUID table, it will trigger to + noofitem in RackID.

This has been handle in the trigger query that i posted. The 2nd (inserted) and 3rd (deleted) query

Test it out and post back if you do encounter any issue.

KH
[spoiler]Time is always against us[/spoiler]

cycheang
Starting Member

40 Posts

Posted - 2011-06-07 : 23:36:05

First Of All,
the trigger work well when i change rackid from old to new.
This work as well when there is mass update for some MatUID.UID from old to new.

I tried to change qty for UID from 2000 to 0, the trigger didnt work.

Existing data
Table: RackID
Column:
RackID NoOfItem
QA13 30

After change of the Qty in MatUID field from 2000 to 0 which
sitted in RackID QA13, the value of NoOfItem in RAckID table won't change from 30 to 29.

Actually the trigger should be work if there is changes on RackID or Qty in MatUID table.
If qty changed and the value is not zero and location remained same, it should not have any trigger.
If qty change from 0 to > 0 then it should reflect the NoOfItem and vice versa.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-08 : 00:11:54

the trigger that i posted does not handle UID qty at all. You can modify it by adding that condition in the WHERE clause.

KH
[spoiler]Time is always against us[/spoiler]

cycheang
Starting Member

40 Posts

Posted - 2011-06-08 : 04:16:43

This is the final version and now it fulfil all the condition.

create trigger ti_MatUID on MatUID for INSERT, UPDATE, DELETE
AS

If (SELECT COUNT(*) FROM inserted Where PlantCode = 'A1' And WhCode= 'W1') > 0 Or
(SELECT COUNT(*) FROM deleted Where PlantCode = 'A1' And WhCode = 'W1') > 0

Begin

If update(RackID) or update(Qty)
begin

update r
set NoOfItem = r.NoOfItem + i.NoOfItem
from (
select PlantCode, WhCode, RackID , NoOfItem = count(*)
from inserted Where cast(Qty as numeric) > 0
group by PlantCode, WhCode, RackID
) i
inner join RackID r on i.PlantCode = r.PlantCode
and i.WhCode = r.WhCode
and i.RackID = r.Location
where i.PlantCode = 'A1'
and i.WhCode = 'W1'

update r
set NoOfItem = r.NoOfItem - d.NoOfItem
from (
select PlantCode, WhCode, RackID , NoOfItem = count(*)
from deleted Where cast(Qty as numeric) <> 0
group by PlantCode, WhCode, RackID
) d
inner join RackID r on d.PlantCode = r.PlantCode
and d.WhCode = r.WhCode
and d.RackID = r.Location
where d.PlantCode = 'A1'
and d.WhCode = 'W1'
end
End

Many thanks for your prompt replied and your help. Will treat you a drink if you come to Penang. :)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-06-08 : 04:25:23

Two small things.

Why are you casting Qty to Numeric? What is it in the table?
You're casting to numeric without defining the scale and precision of that numeric. Do you know what the default is?

--
Gail Shaw
SQL Server MVP

cycheang
Starting Member

40 Posts

Posted - 2011-06-08 : 05:03:44

Before I applying this statement
If update(RackID) or update(Qty)

into trigger, whenver i changed the value on Qty column (data type = Float), it didn't reflected in NoOfItem but it reflected when i changed value in other field (BlockQty - data type defined as numeric)

I had removed the casting to numeric for Qty and it is working now.

cycheang
Starting Member

40 Posts

Posted - 2011-06-14 : 03:38:35

hello khtan,

Just realize that with above trigger, trigger didn't work if i delete the records from the table.

How to cater this, first of all:
1) Trigger will only work if there is insert/delete
2) Trigger will only work if update (applied only on field RackID and Qty)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources