Basic Insted Of Insert Trigger

I am using SQL 2005.
Could you please give me the most basic Instead Of Insert Trigger for
updating a view composed of two Tables. F0r example:
ProductTable Columns are ProductID (int, key) ProductName (varchar)
ProductAttribute Columns are Attribute1, Attribute2, ProductID.
The View is ViewProductTableProductAttribute :
SELECT dbo.ProductTable.ProductID, dbo.ProductTable.ProductName,
dbo.ProductAttribute.ProductAttribute1,
dbo.ProductAttribute.ProductAttribute2
FROM dbo.ProductTable INNER JOIN
dbo.ProductAttribute ON dbo.ProductTable.ProductID =
dbo.ProductAttribute.ProductID
How would a basic Instead Of Insert trigger work here on the View?
Would I need another view to update data to the two tables?
TIA,
PaoloPaul (PaulContactMe@.TheCornerStore.com) writes:
> I am using SQL 2005.
> Could you please give me the most basic Instead Of Insert Trigger for
> updating a view composed of two Tables. F0r example:
> ProductTable Columns are ProductID (int, key) ProductName (varchar)
> ProductAttribute Columns are Attribute1, Attribute2, ProductID.
> The View is ViewProductTableProductAttribute :
> SELECT dbo.ProductTable.ProductID, dbo.ProductTable.ProductName,
> dbo.ProductAttribute.ProductAttribute1,
> dbo.ProductAttribute.ProductAttribute2
> FROM dbo.ProductTable INNER JOIN
> dbo.ProductAttribute ON dbo.ProductTable.ProductID =
> dbo.ProductAttribute.ProductID
> How would a basic Instead Of Insert trigger work here on the View?
Are you asking us to write the requirement spec for you?
Seriously, you know the tables and the business rules. You may not
the syntax, but you know what you want to happen when you say:
INSERT view (...)
SELECT ...
So give us the CREATE TABLE, some sample data (more than one row!)
and the desired result. And some short description in text of what
you want to happen.

> Would I need another view to update data to the two tables?
A somewhat funny question. That makes me ask, why did you add a
view in the first place? I see little reason to create a second
view for the UPDATE. Then again, I don't know the busines rules.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Actually, I am experimenting. I think this is a technology, and am
curious about how it works.
Thanks,
Paul
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97F23FEBA187Yazorman@.127.0.0.1...
> Paul (PaulContactMe@.TheCornerStore.com) writes:
> Are you asking us to write the requirement spec for you?
> Seriously, you know the tables and the business rules. You may not
> the syntax, but you know what you want to happen when you say:
> INSERT view (...)
> SELECT ...
> So give us the CREATE TABLE, some sample data (more than one row!)
> and the desired result. And some short description in text of what
> you want to happen.
>
> A somewhat funny question. That makes me ask, why did you add a
> view in the first place? I see little reason to create a second
> view for the UPDATE. Then again, I don't know the busines rules.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Basic Example of Insert, Update Trigger

Hello, Can someone please show me a basic example of trigger that will
work with two tables in this manner:
1. On INSERT for Table1, copy a particular column's value to another
column in Table2
2. On UPDATE for Table1, copy this particular column's value to
another column in Table2.
The key field for both tables is Invoice# and will always exist in
both tables.
It seems like there are a couple ways to do this, one with just using
a join and the other using the 'Inserted' table. Could someone please
show me the best approach to this solution? I would really be grateful
and name my next born after you.
Thanks in advance,
Buster
On Jun 13, 6:51 am, Buster Coder <dice_respo...@.hotmail.com> wrote:
> Hello, Can someone please show me a basic example of trigger that will
> work with two tables in this manner:
> 1. On INSERT for Table1, copy a particular column's value to another
> column in Table2
> 2. On UPDATE for Table1, copy this particular column's value to
> another column in Table2.
> The key field for both tables is Invoice# and will always exist in
> both tables.
> It seems like there are a couple ways to do this, one with just using
> a join and the other using the 'Inserted' table. Could someone please
> show me the best approach to this solution? I would really be grateful
> and name my next born after you.
> Thanks in advance,
> Buster
I think you require update in table2 in both the cases
CREATE TRIGGER employee_insupd
ON table1
FOR INSERT, UPDATE
AS
UPDATE T2 SET
col2 = a.col1
FROM table2 T2 , inserted a
WHERE T2.invoiceno = a.invoiceno

Basic Example of Insert, Update Trigger

Hello, Can someone please show me a basic example of trigger that will
work with two tables in this manner:
1. On INSERT for Table1, copy a particular column's value to another
column in Table2
2. On UPDATE for Table1, copy this particular column's value to
another column in Table2.
The key field for both tables is Invoice# and will always exist in
both tables.
It seems like there are a couple ways to do this, one with just using
a join and the other using the 'Inserted' table. Could someone please
show me the best approach to this solution? I would really be grateful
and name my next born after you.
Thanks in advance,
BusterOn Jun 13, 6:51 am, Buster Coder <dice_respo...@.hotmail.com> wrote:
> Hello, Can someone please show me a basic example of trigger that will
> work with two tables in this manner:
> 1. On INSERT for Table1, copy a particular column's value to another
> column in Table2
> 2. On UPDATE for Table1, copy this particular column's value to
> another column in Table2.
> The key field for both tables is Invoice# and will always exist in
> both tables.
> It seems like there are a couple ways to do this, one with just using
> a join and the other using the 'Inserted' table. Could someone please
> show me the best approach to this solution? I would really be grateful
> and name my next born after you.
> Thanks in advance,
> Buster
I think you require update in table2 in both the cases
CREATE TRIGGER employee_insupd
ON table1
FOR INSERT, UPDATE
AS
UPDATE T2 SET
col2 = a.col1
FROM table2 T2 , inserted a
WHERE T2.invoiceno = a.invoicenosql

Basic Example of Insert, Update Trigger

Hello, Can someone please show me a basic example of trigger that will
work with two tables in this manner:
1. On INSERT for Table1, copy a particular column's value to another
column in Table2
2. On UPDATE for Table1, copy this particular column's value to
another column in Table2.
The key field for both tables is Invoice# and will always exist in
both tables.
It seems like there are a couple ways to do this, one with just using
a join and the other using the 'Inserted' table. Could someone please
show me the best approach to this solution? I would really be grateful
and name my next born after you.
Thanks in advance,
BusterOn Jun 13, 6:51 am, Buster Coder <dice_respo...@.hotmail.com> wrote:
> Hello, Can someone please show me a basic example of trigger that will
> work with two tables in this manner:
> 1. On INSERT for Table1, copy a particular column's value to another
> column in Table2
> 2. On UPDATE for Table1, copy this particular column's value to
> another column in Table2.
> The key field for both tables is Invoice# and will always exist in
> both tables.
> It seems like there are a couple ways to do this, one with just using
> a join and the other using the 'Inserted' table. Could someone please
> show me the best approach to this solution? I would really be grateful
> and name my next born after you.
> Thanks in advance,
> Buster
I think you require update in table2 in both the cases
CREATE TRIGGER employee_insupd
ON table1
FOR INSERT, UPDATE
AS
UPDATE T2 SET
col2 = a.col1
FROM table2 T2 , inserted a
WHERE T2.invoiceno = a.invoiceno