triggers - 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

General SQL Server Forums

New to SQL Server Programming

triggers

Author

Topic

Jillsy
Starting Member

20 Posts

Posted - 2006-01-19 : 17:39:57

Hello

I am trying to create a trigger which does the following:-

I have a table STOCK with columns ID, ITEM, PRICE. When i insert a new row i want the trigger to activate if the new row PRICE is lower than all the other existing PRICE entries in the column.

Can anyone help?

Jill

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-19 : 20:04:03

An Insert Trigger will always fired when a new record is inserted. The question is what do you want the trigger to do when your criteria is fulfilled ? Post your table structure and identify the primary key. Also give sample data and expected result

-----------------
'KH'

Jillsy
Starting Member

20 Posts

Posted - 2006-01-20 : 05:39:51

Hi Khtan, here we go...

My table is:-

CREATE TABLE stock(
item_ID INT NOT NULL PRIMARY KEY,
item VARCHAR(20),
price MONEY,
)

When the trigger is activated i want a simple message to say that the item inserted is the lowest priced to date. I am using my command prompt and MSDE by the way. I have written the following trigger which is accepted but doesn't fire when a new item is inserted that is the lowest price in the table:-

CREATE TRIGGER lowest_price on stock
FOR INSERT AS
BEGIN declare @price money
select @price = min(local_price) from inserted
If (@price < select min(local_price) from stock)
begin
print 'Lowest priced item to date'
end
END

Hope this clarifies the problem

Regards

Jill

Jillsy
Starting Member

20 Posts

Posted - 2006-01-20 : 05:48:00

Khtan

The trigger in my last post is wrong it should be

CREATE TRIGGER lowest_price on stock
FOR INSERT AS
BEGIN declare @price money
select @price = min(price) from inserted
If (@price < select min(price) from stock)
begin
print 'Lowest priced item to date'
end
END

Jill

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-20 : 06:06:29

Use Instead of trigger in place of FOR trigger since it wll be executed before the insertion actually happens.
Whereas FOR is similar to AFTER which will be executed after the insertion has happened.

TeenageKicks
Starting Member

9 Posts

Posted - 2006-01-20 : 06:07:11

Hi Jillsy,

I'm not an expert on triggers but I think your problem is that your trigger is firing AFTER the Insert has been performed and so when you are selecting the min(price) you are returning what you have just inserted (if it's the lowest). I'm not too sure how you correct this - I don't think there's a BEFORE arguement. Maybe you might have to change your inner query to exclude where item_id does not equal your inserted item_id.

I hope this helps.

Chris

Jillsy
Starting Member

20 Posts

Posted - 2006-01-20 : 09:22:24

Hi Shallu, thank you for your solution, the trigger fires but the new row doesn't get inserted. Any thoughts?

Regards

Jill

Kristen
Test

22859 Posts

Posted - 2006-01-20 : 10:19:30

If you have an INSTEAD OF trigger you will have to make the actual INSERT yourself (i.e. in the trigger), and also make sure you change the trigger whenever the table is changed (new columns added etc.), so it will generally be more work.

I see no difficulty with doing it AFTER it is inserted. The row you have just inserted will then have the lowest price ... I think its that simple isn't it?

What about if an UPDATE to the table changes the price to become the lowest? I've included that in my suggestion below - that will get the "original price" from the table for any row NOT just inserted, and any row NOT just updated


CREATE TRIGGER dbo.lowest_price
ON dbo.stock
/* WITH ENCRYPTION */
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON

DECLARE @MinNewPrice money,
        @MinOriginalPrice money

    SELECT @MinNewPrice = MIN(price)
    FROM   inserted 

    SELECT @MinOriginalPrice = MIN(price)
    FROM   dbo.stock AS S
           LEFT OUTER JOIN inserted AS I
               ON I.item_ID = S.item_ID
           LEFT OUTER JOIN deleted AS D
               ON D.item_ID = S.item_ID
    WHERE      I.item_ID IS NULL -- NOT a newly inserted item
           AND D.item_ID IS NULL -- NOT a modified price

    IF @MinNewPrice < @MinOriginalPrice
    BEGIN
        PRINT 'Lowest priced item to date'
    END
END

Note: I have not through about what would happen for an update that changed the item_ID of an existing row.

Kristen

TeenageKicks
Starting Member

9 Posts

Posted - 2006-01-20 : 10:40:57

Jillsy,

As I mentioned earlier if you change your trigger to this, it should work:

CREATE TRIGGER lowest_price on stock
FOR INSERT AS
BEGIN
declare @price money
declare @item_id int
select @price = price from inserted
select @item_id = item_id from inserted
If (@price < select min(price) from stock where item_id <> @item_id)
begin
print 'Lowest priced item to date'
end
END

Regards,

Chris

Jillsy
Starting Member

20 Posts

Posted - 2006-01-20 : 10:49:36

Thanks guys, much appreciated. Am new to this sql malarky so it takes a little while to get my head round it.

Thanks again

Jill

Kristen
Test

22859 Posts

Posted - 2006-01-20 : 11:10:41

TeenageKicks: "select @item_id = item_id from inserted"

Sorry, that won't work as the trigger needs to handle the scenario of multiple rows being saved, and thus the trigger needs to handle the case where "inserted" contains multiple rows.

Kristen

Jillsy
Starting Member

20 Posts

Posted - 2006-01-20 : 11:17:55

Hi Chris, i tried your version first because it was easier to understand but it's throwing up syntax errors. I'm using MSDE and my command prompt, would the syntax be different? The syntax errors are near select and one of the brackets.

Jill

Kristen
Test

22859 Posts

Posted - 2006-01-20 : 11:24:33

Apart from my earlier comment about handling multiple records you need to encapsulate SELECT statements that return singleton values within expressions in brackets - so the error you are probably seeing is:

If (@price < (select min(price) from stock where item_id <> @item_id))

Kristen

TeenageKicks
Starting Member

9 Posts

Posted - 2006-01-20 : 11:48:24

Hi Jillsy,

Kristen is correct - I missed the brackets around the Select statement. Kristen is also correct about multiple inserts - if the statement that inserts into the table only inserts one row then it should work but if it inserts multiple rows (eg INSERT INTO stock select * from stock_update)
then it won't work. I had made the assumption that you'll be doing one row at a time for some reason.

Regards,

Chris

Jillsy
Starting Member

20 Posts

Posted - 2006-01-20 : 11:56:10

Thanks Kirsten that worked, you wouldn't believe the hours i've wasted on syntax when learning a new language.

Jill

Kristen
Test

22859 Posts

Posted - 2006-01-20 : 12:02:40

"you wouldn't believe the hours i've wasted on syntax when learning a new language."

Perhaps we can set up a virtual lending-library for the T-shirts I'm sure we've all got!

I should post here after you've given it 5 minutes of effort. You'll probably solve it just by going through the process of constructing the posting

, but if not folk here are pretty good at providing answers.

Kristen

Jillsy
Starting Member

20 Posts

Posted - 2006-01-20 : 13:50:09

Hi guys, SQL is starting to bring tears to my eyes. I have a new problem, very simple again but beyond me at the moment. I want to have a stored procedure that takes in a value (in this case a food item) and updates the price of that that food item in the stock table. I have attempted this and came up with following procdure which compiles OK but doesn't work. Can you help again?

CREATE PROCEDURE sp_replace_price @item varchar(15) as RETURN

UPDATE stock SET price = 1.99 WHERE item = @item

print @item

I run the procedure in the command line with:-
execute sp_replace_price 'pears'

Jill

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-20 : 14:02:05

CREATE PROCEDURE sp_replace_price
(@item varchar(15))
AS

UPDATE stock SET price = 1.99 WHERE item = @item

RETURN

GO

BTW, do not prefix your stored procedures with sp_. You can use anything else for the prefix. SQL Server will automatically search the master database for your stored procedure if it is prefixed with sp_. Once it can't find it in master, it will then look in your database. Due to this, there is a small performance hit. We prefix our stored procedures with usp_ to indicate that it is a user stored procedure and not a system one.

Tara Kizer
aka tduggan

Kristen
Test

22859 Posts

Posted - 2006-01-21 : 04:56:53

Also advisable to prefix the Sproc with "dbo." to ensure that there is no ambiguity over the owner (this can effect performance).

So:


CREATE PROCEDURE dbo.usp_replace_price 
(
    @item varchar(15)
)

and


EXECUTE dbo.usp_replace_price @item='MyItemCode'

Kristen

Jillsy
Starting Member

20 Posts

Posted - 2006-01-21 : 16:08:59

Thanks guys, you're spot on again.

Jill

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources