a simple insert/update trigger

Hi I am looking to create a simple trigger for both UPDATES and INSERTS
where any change to tbl1 will be inserted/updated in tbl2.
The insert works okay, with values added to both tables, however when I
perform an update, it seems to add an extra row in tbl2.
Here's the code:
CREATE TABLE [dbo].[tbl1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Team] [varchar] (25) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Team] [varchar] (25) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TRIGGER [tri_DataTrans] ON [dbo].[tbl1]
FOR INSERT, UPDATE
AS
INSERT INTO tbl2([ID], [name], team)
SELECT ID, [name], team FROM inserted
UPDATE tbl2
SET [Name] = Inserted.[Name],
[Team] = Inserted.[Team]
FROM Inserted
WHERE [ID] = Inserted.[ID]
---
What am I doing wrong? do I need to use IF UPDATE()?
Thanks
qh75Why do you want to use a single trigger? You can certainly do that
(modify your INSERT to insert the row only if it doesn't already exist)
but since you want totally different actions in the case of UPDATE and
INSERT it will surely be more efficient to use two triggers instead of
one.
Secondly, your tables as posted have no keys at all. Apparently even
the ID isn't declared as unique (the IDENTITY property doesn't actually
guarantee uniqueness) and the other columns are nullable. Even if that
constrain exists on ID it is definitely not safe to assume that a row
would be assigned the same ID in both tables. On the other hand you
have included the ID in the INSERT, which will fail unless you turn
IDENTITY_INSERT ON, so I'm not clear if you intended to use IDENTITY in
the second table or not.
The solution is to declare natural keys on both tables (presumably Name
and/or Team) and make those column(s) NOT NULL. Then join the two
tables on that key rather than the IDENTITY.
David Portas
SQL Server MVP
--|||David Portas wrote:
> Why do you want to use a single trigger? You can certainly do that
> (modify your INSERT to insert the row only if it doesn't already
exist)
> but since you want totally different actions in the case of UPDATE
and
> INSERT it will surely be more efficient to use two triggers instead
of
> one.
I just thought it could be performed in one trigger, basically (without
getting into the identity stuff) I was looking for some logic I could
use in the one trigger to check for both inserts and both updates.

> Secondly, your tables as posted have no keys at all. Apparently even
> the ID isn't declared as unique (the IDENTITY property doesn't
actually
> guarantee uniqueness) and the other columns are nullable. Even if
that
> constrain exists on ID it is definitely not safe to assume that a row
> would be assigned the same ID in both tables. On the other hand you
> have included the ID in the INSERT, which will fail unless you turn
> IDENTITY_INSERT ON, so I'm not clear if you intended to use IDENTITY
in
> the second table or not.
I'll add a key to the tables and try that.
Thanks for the reply.
qh|||Anyhoo, this is what I came up with:
---
CREATE TRIGGER [tri_Update] ON [dbo].[tbl1]
FOR UPDATE
AS
IF UPDATE ([Team])
BEGIN
UPDATE tbl2
SET [Team] = Inserted.[Team]
FROM tbl2, Inserted
WHERE tbl2.[ID] = Inserted.[ID]
END
IF UPDATE ([Name])
BEGIN
UPDATE tbl2
SET [Name] = Inserted.[Name]
FROM tbl2, Inserted
WHERE tbl2.[ID] = Inserted.[ID]
END
----
CREATE TRIGGER [tri_DataTrans] ON [dbo].[tbl1]
FOR INSERT
AS
INSERT INTO tbl2([ID], [name], team)
SELECT ID, [name], team FROM inserted
----
If you know of a way of combining the two triggers into one (if indeed
it can be done) please let me know.
Cheers
qh