Is it possible to execute an SQL syntax with a trigger, and how?

[stigejg]

Hi,

I am quiet new with SQL server programming, and I am not familiar with building up triggers and so on.

I have a couple of questions:

A couple of days ago I got a wery helpful tips from "Terry L. Broadbent" with this SQL syntax:

Update pr Set [Transaction Price] = pl.[Costing sum 1]
From Production pr
Join Pricelist pl
On pr.Company = pl.Company
And pr.Facility = pl.Facility
And pr.[Component number] = pl.[Item number]
And pr.[Start date] BETWEEN pl.[Costing date from] AND pl.[Costing date to]

So my first question is:

Is it possible to build a Trigger that executes this Syntax when my production table is updated? And can anyone of you give me an simple example of how this trigger will look like?

My second question is:

Is it possible to execute two or moore update-syntaxes in just one trigger?

Anyone out there wuo can give me a tips about this?

Best regards Stig

 

[SQLSister]

YOu can look up the trigger syntax in books online. But once you have put in the create trigger syntax, the sql you use within the trigger is just like what you would use outside the trigger.

Triggers have one special feature though which is useful in coding the trigger. Often you want to know just what records are being created or updated or deleted, so triggers have two pseudotables that are only accessible from within triggers called inserted and deleted.

Inserted holds the new data that is inserted into the table, deleted holds the old data which is being deleted. In the case of an update, both tables are available, in a delete trigger there is only data in the deleted table and in an insert, there is only data in the inserted table. You can refer to these tables the same way you refer to other tables.

Another thing to be careful with triggers is account for more than one record being inserted or deleted. Some people write code which will only work if one record is inserted or updated or deleted. Unfortunately, tables often have mutliple records being acted on at one time. For instance that update statement you got from Terry would probably update multiple records.

 

[stigejg]

Hi SQLSister,

I have looked around in books online and tryed to figoure out how to solve this case. But I am not familiar with Transact SQL and how I have to use the parameters for firing the trigger.

And you are right, the SQL syntax are supposed to update several records. The production table are rebuilt every night by an ETL-job and I am hoping to have a trigger to update the price-fields after that.

Stig