SQL Server Trigger - 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

SQL Server Trigger

Author

Topic

UtahJones
Starting Member

4 Posts

Posted - 2010-02-20 : 13:54:33

I need to create a simple trigger to update a field in a table if the extended cost is greater than a certain value.

The table name is OrderDetailsTbl, the field to be analyzed is called Extended Cost and the field to update is called Discount.

After an update is done to the field Extended Cost and the Extended Cost value is >300 I need to have the field Discount="Yes"

I'm using SQL Server 2008 Express.

Any help would be appreciated.

Sachin.Nand

2937 Posts

Posted - 2010-02-20 : 14:14:08

Why u wanna use a trigger?
You can do it in the same statement where you are updating the field Extended Cost.

PBUH

UtahJones
Starting Member

4 Posts

Posted - 2010-02-20 : 14:23:30

Thank you. I am aware that I can easily do this on the form itself in Access. I want to create a trigger because I am trying to teach myself more about SQL Server stored procedures and triggers. I thought creating this first very simple trigger would help me get started, then I want to create more complex triggers. I'm trying to get deeper in SQL Server.

Thank you!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-02-20 : 14:42:27

Show us what you have so far and we'll help you get it working.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Sachin.Nand

2937 Posts

Posted - 2010-02-20 : 14:55:15

quote:
Originally posted by UtahJones

Thank you. I am aware that I can easily do this on the form itself in Access. I want to create a trigger because I am trying to teach myself more about SQL Server stored procedures and triggers. I thought creating this first very simple trigger would help me get started, then I want to create more complex triggers. I'm trying to get deeper in SQL Server.

Thank you!

But do u know anything about triggers.I mean different type of triggers.Magic tables like Inserted ,Deleted etc.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-02-20 : 14:59:48

[code]
create trigger MyfirstTrigger on Mytable
for update as

if exists(select 1 from inserted where ExtendedCost>300)

set nocount on

begin

update Mytable set Discount='Yes'

end

[/code]

PBUH

UtahJones
Starting Member

4 Posts

Posted - 2010-02-20 : 15:13:28

Thank you. I don't have any code written. The table is: OrderDetailsTbl
The fields are: [Extended Cost] and Discount. If after an update and the [Extended cost] is >300 then set the Discount field="Yes"

UtahJones
Starting Member

4 Posts

Posted - 2010-02-20 : 15:20:53

Thank you PBUH. I will use what you sent as a example for developing the trigger. Thank you!

Sachin.Nand

2937 Posts

Posted - 2010-02-20 : 15:24:55

quote:
Originally posted by UtahJones

Thank you PBUH. I will use what you sent as a example for developing the trigger. Thank you!

You are welcome.

By the way my SQLTeam member name is Idera and PBUH is the signature

PBUH

Kristen
Test

22859 Posts

Posted - 2010-02-21 : 03:51:05

Note that IDera's example will set the [Discount] column on all rows in MyTable to "Yes".

You didn't say quite how you wanted that to work, but if you have rows in MyTable matching the IDs in OrderDetailsTbl then you could do:


create trigger MyfirstTrigger on Mytable
for  update as	
set nocount on
if exists(select 1  from inserted where ExtendedCost>300)
begin

update U
set Discount='Yes'
FROM Mytable AS U
    JOIN inserted AS I
        ON U.TheID_Column = I.TheID_Column
       AND I.ExtendedCost > 300

end

Sachin.Nand

2937 Posts

Posted - 2010-02-21 : 04:58:10

quote:
Originally posted by Kristen

Note that IDera's example will set the [Discount] column on all rows in MyTable to "Yes".

You didn't say quite how you wanted that to work, but if you have rows in MyTable matching the IDs in OrderDetailsTbl then you could do:

create trigger MyfirstTrigger on Mytable
for update as
set nocount on
if exists(select 1 from inserted where ExtendedCost>300)
begin

update U
set Discount='Yes'
FROM Mytable AS U
JOIN inserted AS I
ON U.TheID_Column = I.TheID_Column
AND I.ExtendedCost > 300

end

Oops sorry I missed on that one.

Was kind of late in night here in India that time.

Thanks for the catch Kristen.

PBUH

Kristen
Test

22859 Posts

Posted - 2010-02-21 : 05:34:01

Not really a catch, the O/Ps requirements are not obvious as yet

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources