trigger help - SQL Server Forums

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

SQL Server Development (2000)

trigger help

Author

Topic

jmiller
Starting Member

7 Posts

Posted - 2007-01-31 : 12:28:44

I need help on a trigger that will update a tables value if the skew number is exists, if not, add it to the table. My information is below, I have tried many triggers, but all without results I am looking for. I just need a little help thinking outside the box.

tbl_inv
skew(nvarchar)
cases(int)
skids(int)

tbl_enter
id(int)
skew(nvarchar)
cases(int)
skids(int)
date(date)

Basically, When a record is inserted into tbl_enter it will fire a trigger that inserts skew(nvarchar),cases(int),skids(int) into tbl_inv. If skew already exists in tbl_inv it will add the inserted data to the current data.

Example:
Before Data inserted into tbl_enter
tbl_inv
FCA6850-01 50 75
28113-26000P 25 50
FCA4939-01 75 100

Inserted data into tbl_enter
FCA6850-01 10 20

After Data inserted into tbl_enter and trigger fires
tbl_inv
FCA6850-01 60 95
28113-26000P 25 50
FCA4939-01 75 100

But if the skew does not exists in the table it just adds it in.

Example:
Before Data inserted into tbl_enter
FCA6850-01 50 75
28113-26000P 25 50
FCA4939-01 75 100

Inserted data into tbl_enter
ZZZZZ-01 15 20

After Data inserted into tbl_enter and trigger fires
FCA6850-01 60 95
28113-26000P 25 50
FCA4939-01 75 100
ZZZZZ-01 15 20

This is what I think the trigger should look like, but wrong


CREATE TRIGGER [skew_inv] ON tbl_enter
FOR INSERT
AS
INSERT tbl_inv (skew,cases,skids)
Select skew,+cases,+skids
From inserted
where skew=i.skew

sshelper
Posting Yak Master

216 Posts

Posted - 2007-01-31 : 12:38:00

Try this trigger:

CREATE TRIGGER [skew_inv] ON tbl_enter
FOR INSERT
AS

-- Step #1 : Insert all Skews that doesn't exist, with cases and skids set to 0
INSERT tbl_inv (skew,cases,skids)
Select skew, 0, 0
From inserted I
WHERE NOT EXISTS (SELECT 'X' FROM tbl_inv Inv WHERE I.Skew = Inv.Skew)

-- Step #2 : Add the Cases and Skids
UPDATE A
SET Cases = Cases + B.Cases,
Skids = Skids + B.Skids
FROM tbl_inv A INNER JOIN inserted B ON A.skew = B.skew

SQL Server Helper
http://www.sql-server-helper.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources