Best way to fire off a trigger when a tabe is compleated? 1

[stigejg]

Hi,

I have two tables on a datavarehus server that goes trough a full refresh every night by an ETL job. These tables are

"Production" that contains production data and a "Pricelist" that contains our standardprices.

To save time and capasity i just put 0 in to the [transaction price] coloumn in order to update this by running a trigger

afterwards. But I am not too familiar with trigger and trigger events so i have to collect some tips from someone other.

I have defined this trigger, but haven't had the courage to run it on the server yet.

Can anyone of you out there tell me if this trigger will work, my intent is to fire off this trigger right after the ETL-job?

CREATE TRIGGER Uppdate_Price_On_Productiondata
AFTER UPDATE ON [Production]
FOR EACH STATEMENT
BEGIN
Update A Set [Transaction Price] = B.[Costing sum 1]
From Production A
Join Pricelist B
On A.Company = B.Company
And A.Facility = B.Facility
And A.[Component number] = B.[Item number]
And A.[Start date] BETWEEN B.[Costing date from] AND B.[Costing date to]
END

Further, Is it possible to use "AFTER COMMIT TRANS" as a trigger event? Because I want to fire off this trigger right after

the ETL job has finished rebuild my two tables. Or is there an another triggerevent that is better to use thav the one I have

choosen in my trigger.

I will be grateful for every comment

Best regards Stig

 

[JamesLean]

Are you using SQL Server? The syntax for your trigger is not correct if you are. Also, what is an "ETL job"?

--James

 

[stigejg]

yes I am using SQL server.
ETL-job is av datawarehouse job that moves data from one datsource, does the necessary modification before it is stored in an new datasource. This job is scheduled and automatic when it is set up.

Stig

 

[JamesLean]

It doesn't sound like a trigger is what you need for this. I would put your update query in a stored proc and then just get the import job to fire the SP after the main import is done.

--James

 

[stigejg]

Hi james, this is interesting

Something like this?

CREATE PROC Uppdate_Price_On_Productiondata
AS
UPDATE A SET [Transaction Price] = B.[Costing sum 1]
FROM Production A
JOIN Pricelist B
ON A.Company = B.Company
AND A.Facility = B.Facility
AND A.[Component number] = B.[Item number]
AND A.[Start date] BETWEEN B.[Costing date from] AND B.[Costing date to]

Would this procedure work?

Stig

 

[JamesLean]

Well the syntax looks fine - assuming the logic is correct then yes it should work!

--James

 

[stigejg]

Hi James,

Thank you for a useful tip, It's just been tested and worked like a dream within the ETL-job. Lots of stars to you.

Stig