January 13, 2012 at 1:06 pm
create trigger jam_spu_csp_insert
after insert on customer_special_prices
referencing new as new_name
for each row
begin
insert into jam_spu ( customer_id, location_number, seq_num )
values ( new_name.customer_id, new_name.location_number, new_name.seq_num )
end
go
create trigger jam_spu_csp_changed
after update of price_table, bracket
order 2 on customer_special_prices
referencing old as old_name
for each row
begin
insert into jam_spu ( customer_id, location_number, seq_num )
values ( old_name.customer_id, old_name.location_number, old_name.seq_num )
end
go
create trigger jam_spu_im_changed
after update of price_table, vpp_unit, freight_unit, overhead_unit, rebate_unit,
standard_cost_unit, mfr_cost_unit, num_of_unit_equiv, mfr_sug_list_price
on inventory_master
referencing old as old_name
for each row
begin
insert into jam_spu ( customer_id, location_number, seq_num )
values ( '', 0, old_name.seq_num )
end
go
January 13, 2012 at 2:32 pm
Duplicate post. See this thread
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 13, 2012 at 2:38 pm
Here is the 1st one :
create trigger jam_spu_csp_insert
on customer_special_prices
after insert
as
begin
insert into jam_spu ( customer_id, location_number, seq_num )
SELECT
customer_id, location_number, seq_num
FROM inserted
end
go
January 13, 2012 at 2:51 pm
Thank you very much can you tell me rest of the two triggers
create trigger jam_spu_csp_changed
after update of price_table, bracket
order 2 on customer_special_prices
referencing old as old_name
for each row
begin
insert into jam_spu ( customer_id, location_number, seq_num )
values ( old_name.customer_id, old_name.location_number, old_name.seq_num )
end
go
create trigger jam_spu_im_changed
after update of price_table, vpp_unit, freight_unit, overhead_unit, rebate_unit,
standard_cost_unit, mfr_cost_unit, num_of_unit_equiv, mfr_sug_list_price
on inventory_master
referencing old as old_name
for each row
begin
insert into jam_spu ( customer_id, location_number, seq_num )
values ( '', 0, old_name.seq_num )
end
go
January 13, 2012 at 3:09 pm
venu9099 (1/13/2012)
Thank you very much can you tell me rest of the two triggerscreate trigger jam_spu_csp_changed
after update of price_table, bracket
order 2 on customer_special_prices
referencing old as old_name
for each row
begin
insert into jam_spu ( customer_id, location_number, seq_num )
values ( old_name.customer_id, old_name.location_number, old_name.seq_num )
end
go
create trigger jam_spu_im_changed
after update of price_table, vpp_unit, freight_unit, overhead_unit, rebate_unit,
standard_cost_unit, mfr_cost_unit, num_of_unit_equiv, mfr_sug_list_price
on inventory_master
referencing old as old_name
for each row
begin
insert into jam_spu ( customer_id, location_number, seq_num )
values ( '', 0, old_name.seq_num )
end
go
Let's try but I will need some inputs first :
- About jam_spu_csp_changed :
Are price_table, bracket fields from customer_special_prices table ?
Do you have any idea of what "order 2" means ?
- About jam_spu_im_changed, it should be :
create trigger jam_spu_im_changed
on inventory_master
after UPDATE
AS
BEGIN
IF UPDATE(price_table) OR UPDATE(vpp_unit) -- to be continued ... , freight_unit, overhead_unit, rebate_unit,standard_cost_unit, mfr_cost_unit, num_of_unit_equiv, mfr_sug_list_price
insert into jam_spu ( customer_id, location_number, seq_num )
SELECT '', 0, seq_num FROM deleted
end
go
January 13, 2012 at 3:22 pm
Are price_table, bracket fields from customer_special_prices table ?
Yes those columns are from customer_special_prices table
Do you have any idea of what "order 2" means ?
I have no idea but i think it is order by clause
January 13, 2012 at 3:32 pm
I just had a look at sybase online documentation (http://manuals.sybase.com/onlinebooks/group-pbarc/conn5/sqlug/@Generic__BookTextView/42763;pt=41158), and Order 2 means that there must be at least 2 after update triggers on this table and this one will fire on 2nd place.
Do you have another update trigger on that table ?
In SQL Server, you can have multiple "AFTER UPDATE" triggers but to my knowledge, you can't control the order they will be fired !
So you could have to mix this trigger and another one.
If there is no other "AFTER UPDATE" triggers for that table, you can forget the option.
And you have enough information with my previous answers to translate it.
January 13, 2012 at 3:35 pm
CAN I WRITE LIKE THIS
create trigger jam_spu_csp_changed
on customer_special_prices
after
update
as
begin
IF UPDATE(price_table)OR UPDATE (bracket)
insert into jam_spu ( customer_id, location_number, seq_num )
select customer_id,location_number, seq_num FROM updated
end
go
January 13, 2012 at 3:38 pm
I don't have updated trigger but I have insert trigger
create trigger jam_spu_csp_insert on customer_special_pricesafter insert asbegininsert into jam_spu ( customer_id, location_number, seq_num )SELECT customer_id, location_number, seq_num FROM insertedendgo
January 13, 2012 at 3:47 pm
venu9099 (1/13/2012)
CAN I WRITE LIKE THIScreate trigger jam_spu_csp_changed
on customer_special_prices
after
update
as
begin
IF UPDATE(price_table)OR UPDATE (bracket)
insert into jam_spu ( customer_id, location_number, seq_num )
select customer_id,location_number, seq_num FROM updated
end
go
Yes, it looks good.
venu9099 (1/13/2012)
I don't have updated trigger but I have insert triggercreate trigger jam_spu_csp_insert on customer_special_pricesafter insert asbegininsert into jam_spu ( customer_id, location_number, seq_num )SELECT customer_id, location_number, seq_num FROM insertedendgo
Online doc says :
Order of firing
Triggers of the same type (insert, update, or delete) that fire at the same time (before, after, or resolve) can use the ORDER clause to determine the order that the triggers are fired.
So I think we canforget that option.
January 27, 2012 at 8:46 am
when I created the trigger I am getting below error when I am testing
SQL STATE=42S02
MICROSOFT SQL NATIVE CLIENT
INVALID OBJECT NAME 'UPDATED'
NO CHANGES MADE TO DATABASE
CAN YOU HELP ME ON THIS ERROR
January 27, 2012 at 8:59 am
Replace updated with :
- deleted if you want work with old values.
- inserted if you want new ones.
February 14, 2012 at 4:39 am
Perl language is the most useful administration language on the Unix/Linux environment. Since Web technologies have risen in popularity, Perl also became a main development language for Internet-based development. Perl programming, such as CGI, Fast-CGI, mod_perl, Apache::ASP (very similar to Microsoft’s ASP 3.0), and Embedded-perl are used to create dynamic web pages and have become very successful web solutions.Today’s website cannot leave out database support. Combining Perl and SQL Server (Sybase or Microsoft) is a natural way for those companies who want to migrate from traditional client-server architectures to Internet-based architectures for expanding their business. New companies also benefit from low ownership and cost by choosing a Perl and SQL Server solution.
Detail This
http://databases.assistprogramming.com/executing-sql-server-stored-procedure-from-perl.html
http://en.wikipedia.org/wiki/Microsoft_SQL_Server
Search Engine Optimizing | Search Engine Marketing | Social Media Marketing | Pay Per Clicks
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply