Help with Trigger

[Sammy]

I would really appreciate some help with a trigger

I have a Soheader table and a customer table.

I would like upon data entry to update the Soheader.sotypeid field with the
customer.user5 field when the soheader.custid is selected.

so upon entry or change of the soheader.custid I would like the
Soheader.sotypeid field to get updated with the value on the customer.user5
field.
where soheader.custid = customer.custid

thanks very much

S Commar

[Erland Sommarskog]

CREATE TRIGGER Sammy_tri ON customer AFTER INSERT, UPDATE AS

IF UPDATE(user5)
BEGIN
UPDATE soheader
SET sotypeid = i.user5
FROM soheader s
JOIN inserted i ON s.custid = c.custid
END

The table "inserted" is a virtual table that holds the row that were
inserted, or in case of an UPDATE trigger an after-image of the updated
rows.

Likewise there is a "deleted" table with the deleted rows or a before-image
of the update rows in an UPDATE trigger.

Also keep in mind that triggers fires once per statement.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Sammy]

Erland

Thanks very much.

What I wanted to do was update the Soheader.custid when I am entereing
records in Soheader as the customer.user5 is already populated.
So it not like I am trying to update the Soheader when I change or add the
customer.user5 but more so that the soheader.user5 should get populated when
I am entering the Soheader record.

Thanks

Sammy

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9DE37602A...@127.0.0.1...

[Erland Sommarskog]

Sammy (s_co...@hotmail.com) writes:
> What I wanted to do was update the Soheader.custid when I am entereing
> records in Soheader as the customer.user5 is already populated. So it
> not like I am trying to update the Soheader when I change or add the
> customer.user5 but more so that the soheader.user5 should get populated
> when I am entering the Soheader record.

In the previous post you said you wanted to update sotypeid, now you want
to update custid?

I hope that my example is enough to help you to write the trigger you need.
I'm sorry that it was not exactly what you needed, but your post was a
bit ambiguous.

[Sammy]

No that is not correct. I have said I want to update soheader.sotypeid
field in both posts.
and no your example is not helpful.

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message

news:Xns9DE38D9CC...@127.0.0.1...

[Erland Sommarskog]

Sammy (s_co...@hotmail.com) writes:
> No that is not correct. I have said I want to update soheader.sotypeid
> field in both posts.

Well in your last post you said "What I wanted to do was update the
Soheader.custid". Which I interpreted that you want to update the custid
column. "sotypeid" is not mentioned in that post...

> and no your example is not helpful.

Well, I have this idea that people ask to learn, and thus are intrested
in using an example to work from. But clearly some expects to be spoon-fed.
Which I have to say I find less encouraging.

But maybe this is what you want:

CREATE TRIGGER Sammy_tri ON soheader AFTER INSERT AS
UPDATE soheader
SET sotypeid = (SELECT c.user5
FROM customers c
WHERE c.custid = s.custid)
FROM soheader s
JOIN inserted i ON s.keycol = i.keycol

You did not say what is the primary key on soheader, so you will need
to fill in that yourself