sql server msde: Trigger help -- sql server msde

You're in the

sql server msde

group:

Trigger help

Danny
7/9/2007 12:06:31 PM

sql server msde:

Trying to make a trigger - not sure of right syntax...

Table ITEM
ID
Description nvarchar(30)
ItemLookupCode nvarchar(25)
DepartmentID int
CategoryID int
Price money
PriceA money
PriceB money
TaxID int
ItemType smallint
Cost money

-----------------------------------------------------
CREATE TRIGGER InsertILCFQ
ON Item
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @NewILCNum
SET @NewILCNum = insertedILC, "FQ"
SELECT @DptmentID =
IF @DptmentID <> 7 /* Wrong department, exit trigger */
RETURN
INSERT into Item
(Description, ItemLookupCode, DepartmentID, CategoryID, Price, PriceA,
PriceB, TaxID, ItemType , Cost)
values
('Fat Quarter', @NewILCNew, 7, 22, InsertedPrice / 4, InsertPriceB / 4,
InsertedPriceB /4, 1, 3, InsertedCost /4)
/* InsertedPrice, etc should be the price from the insert statement */
END
------------------------------------------------------------

Re: Trigger help

Andrea Montanari
7/9/2007 6:17:17 PM

hi Danny,
[quoted text, click to view]

why are you trying such a kind of trigger (beyond the wrong syntax, but this
is another story)..

I do actually not understand if you are trying to write an INSTEAD OF
trigger or just an AFTER trigger..
as you already know, an AFTER trigger is executed after the actual DML
operation, in this case just an INSERT statement, so you do not have to
"replicate" it inserting again a row in the very same table the trigger is
related to..
if, on the contrary, you need an INSTEAD OF trigger, you could come up to
something similar to the following,
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.ITEM (
ID int NOT NULL IDENTITY,
[Description] nvarchar(30),
ItemLookupCode nvarchar(25),
DepartmentID int,
CategoryID int,
Price money,
PriceA money,
PriceB money,
TaxID int,
ItemType smallint,
Cost money
);
GO
CREATE TRIGGER InsertILCFQ ON Item
INSTEAD OF INSERT
AS BEGIN
DECLARE @r int;
SET @r = @@ROWCOUNT;

IF @r = 0 RETURN;

IF @r > 1 BEGIN
RAISERROR ('Only 1 row can be inserted; rows to be processed: %d', 16, 1,
@r );
RETURN;
END;

SET NOCOUNT ON;
DECLARE @NewILCNum varchar(25);
SET @NewILCNum = 'FQ';

DECLARE @DptmentID int;
SELECT @DptmentID = DepartmentID
FROM inserted;

IF @DptmentID <> 7 BEGIN
/* Wrong department, exit trigger */
RAISERROR ('Invalid detartment code %d', 16, 1, @DptmentID );
RETURN;
END;

INSERT INTO dbo.ITEM
([Description], ItemLookupCode, DepartmentID, CategoryID, Price, PriceA,
PriceB, TaxID, ItemType , Cost)
SELECT 'Fat Quarter',
@NewILCNum,
7,
22,
Price / 4,
PriceB / 4,
PriceB /4,
1,
3,
Cost /4
FROM inserted;

END;
GO
INSERT INTO dbo.ITEM VALUES ( 'desc', 'lk', 99, 99, 100, 110, 120, 99, 99,
130 );
GO
INSERT INTO dbo.ITEM VALUES ( 'desc', 'lk', 7, 99, 100, 110, 120, 99, 99,
130 );
GO
SELECT * FROM dbo.ITEM;
GO
DROP TABLE dbo.ITEM;
--<--------
Msg 50000, Level 16, State 1, Procedure InsertILCFQ, Line 16
Invalid detartment code 99
ID Description ItemLookupCode
DepartmentID CategoryID Price PriceA PriceB
TaxID ItemType Cost
----------- ------------------------------ ------------------------- ------------
----------- --------------------- --------------------- ---------------------
----------- -------- ---------------------
1 Fat Quarter FQ 7
22 25,00 30,00 30,00
1 3 32,50

but, to me, it's a "non sense" as well, as you could (and should) define the
appropriate business logic (if any) in the middle tier or (better, in my
mind) in a stored procedure..
remember it's often quite difficult to code a good mutli-rows trigger.. the
one you are trying to code seems to require variables and thus at least a
"cycle" on the inserted row(s)... the presented one will fail if more than a
row is inserted at the same time..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
--------- remove DMO to reply

Re: Trigger help

Danny
7/9/2007 7:22:13 PM

Hi Andrea

Thanks for your help - is this any better?

I was trying to do an After trigger - creating a new ILC ItemLookupCode with
the name of the original-itemlookupcode plus "FQ" ... In other words, the
initial DML would do an insert and my trigger would do another insert with a
slightly different ILC, initial-inserted-price/4, description, etc...

The applications business logic handles the first insert of the ILC - my
trigger would do the second ILC which is closely linked the first item.
saving me from doing two data entry for this departmentID..

Hope this clears up what I am trying to accomplish.

CREATE TRIGGER InsertILCFQ
ON Item
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @NewILCNum
SET @NewILCNum = insertedILC, "FQ"
/* concatenate FQ to the inserted ILC */

DECLARE @DptmentID int;
SELECT @DptmentID = DepartmentID
FROM inserted;

SELECT @DptmentID =
IF @DptmentID <> 7 /* only execute trigger inserts on department 7 */
RETURN

INSERT into dbo.item
([Description], ItemLookupCode, DepartmentID, CategoryID, Price, PriceA,
PriceB, TaxID, ItemType , Cost)
values
SELECT 'Fat Quarter',
@NewILCNum,
DepartmentID,
22,
Price / 4,
PriceA / 4,
PriceB /4,
TaxID,
3,
Cost /4
FROM inserted;
/* only Description, ILC, CategoryID, ItemType really change as well as
price and cost are divided by 4*/
END

[quoted text, click to view]

Re: Trigger help

Danny
7/10/2007 12:00:00 AM

Hi Hugo

Thanks, for the help - this looks actually what I need. I will test and
double test just to make sure.

Just wasn't sure on the syntax of reusing inserted fields.

Danny
[quoted text, click to view]

Re: Trigger help

Hugo Kornelis
7/10/2007 12:32:49 AM

[quoted text, click to view]

Hi Danny,

Something like the below - which handles multi-row updates as well.

Do check, double-check, and test, since your requirements are far from
clear to me!

CREATE TRIGGER InsertILCFQ
ON Item
AFTER INSERT
AS
BEGIN;
SET NOCOUNT ON;
INSERT INTO dbo.item (column list here)
SELECT 'Fat Quarter',
i.ILC + 'FQ',
i.DepartmentID,
22,
i.Price / 4,
i.PriceA / 4,
i.TaxID,
3,
i.Cost / 4
FROM inserted AS i
WHERE i.DepartmentID <> 7;
END;
go

--
Hugo Kornelis, SQL Server MVP

Re: Trigger help

Danny
7/12/2007 11:42:24 AM

Trying to add more functionality to the following trigger. Need to insert
three field into another table using the ID from the first insert (i.id),
the ID from the Trigger Insert (??) and a QTY value of .25? My question is
how do I get the ID from the trigger insert?

ITEM Table
ID int
Description nvarchar(30)
ItemLookupCode nvarchar(25)
etc, .....

Kit Table
KitItemID int = ID from Trigger insert
ComponentItemID int = ID from INsert before trigger i.id
Quantity float .25

In other words, would like to add some thing like this at the end of the
trigger?:
INSERT INTO dbo.kit
(KitItemID, ComponentItemID, Quantity)
Values
(?,?,.25)

----------------------------------------------------------------------------
CREATE TRIGGER InsertILCFQ
ON Item
AFTER INSERT
AS
BEGIN;
SET NOCOUNT ON;
INSERT INTO dbo.item
([Description],
ItemLookupCode,
DepartmentID,
CategoryID,
Price,
PriceA,
PriceB,
TaxID,
ItemType,
Cost)
SELECT 'Fat Quarter',
i.ItemLookupCode + 'FQ',
i.DepartmentID,
22,
i.Price / 4,
i.PriceA / 4,
i.PriceB /4,
i.TaxID,
3,
i.Cost / 4
FROM inserted AS i
WHERE i.DepartmentID = 7;
END;
go

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server msde group.

home · blog · groups

Questions? Comments? Contact the dn