update usr_smc
set country='United Kingdom'
where country='Great Britain (UK)' or country='Great Britain'

Server: Msg 512, Level 16, State 1, Procedure usr_smc_Update, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usr_smc_Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[usr_smc_Insert]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usr_smc_Update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[usr_smc_Update]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usr_smc_Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[usr_smc_Delete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usr_smc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[usr_smc]
GO

CREATE TABLE [dbo].[usr_smc] (
	[user_id] [int] NOT NULL ,
	[firstname] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[lastname] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[email] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[address_1] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[address_2] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[city] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[state] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[zip] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[country] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[title] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[job] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[industry] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[company_name] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[hear_about] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[info_add_products] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[info_release_products] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[info_events] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[info_training] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[phone] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[fax] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[phone_ext] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cad_primary] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cad_primary_version] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cad_secondary] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cad_secondary_version] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cad_other] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cad_other_version] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[phone_country_code] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[record_state] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[country_code] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[district] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[province] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Second_Last_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[title_2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[middle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[area_Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[contact_me] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[usr_smc] ADD 
	CONSTRAINT [PK_usr_smc] PRIMARY KEY  NONCLUSTERED 
	(
		[user_id]
	) WITH  FILLFACTOR = 96  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[usr_smc] ADD 
	CONSTRAINT [FK_usr_smc_usr] FOREIGN KEY 
	(
		[user_id]
	) REFERENCES [dbo].[usr] (
		[user_id]
	)
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

quote:

---

Originally posted by tduggan

Could you post the triggers? According to the error, that's where the problem is.

Tara

---

CREATE TRIGGER [usr_smc_Insert] ON dbo.usr_smc
FOR INSERT
AS
INSERT SMC_New_Products.dbo.usr_smc (
	user_id,
	firstname,
	lastname,
	email,
	address_1,
	address_2,
	city,
	state,
	zip,
	country,
	title,
	job,
	industry,
	company_name,
	hear_about,
	info_add_products,
	info_release_products,
	info_events,
	info_training,
	phone,
	fax,
	phone_ext,
	cad_primary,
	cad_primary_version,
	cad_secondary,
	cad_secondary_version,
	cad_other,
	cad_other_version,
	phone_country_code,
	record_state)

SELECT
	user_id,
	firstname,
	lastname,
	email,
	address_1,
	address_2,
	city,
	state,
	zip,
	country,
	title,
	job,
	industry,
	company_name,
	hear_about,
	info_add_products,
	info_release_products,
	info_events,
	info_training,
	phone,
	fax,
	phone_ext,
	cad_primary,
	cad_primary_version,
	cad_secondary,
	cad_secondary_version,
	cad_other,
	cad_other_version,
	phone_country_code,
	record_state
FROM inserted

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER [usr_smc_Update] ON dbo.usr_smc
FOR UPDATE
AS
UPDATE SMC_New_Products.dbo.usr_smc 
	SET
		firstname = (SELECT firstname FROM inserted),
		lastname = (SELECT lastname FROM inserted),
		email = (SELECT email FROM inserted),
		address_1 = (SELECT address_1 FROM inserted),
		address_2 = (SELECT address_2 FROM inserted),
		city = (SELECT city FROM inserted),
		state = (SELECT state FROM inserted),
		zip = (SELECT zip FROM inserted),
		country = (SELECT country FROM inserted),
		title = (SELECT title FROM inserted),
		job = (SELECT job FROM inserted),
		industry = (SELECT industry FROM inserted),
		company_name = (SELECT company_name FROM inserted),
		hear_about = (SELECT hear_about FROM inserted),
		info_add_products = (SELECT info_add_products FROM inserted),
		info_release_products = (SELECT info_release_products FROM inserted),
		info_events = (SELECT info_events FROM inserted),
		info_training = (SELECT info_training FROM inserted),
		phone = (SELECT phone FROM inserted),
		fax = (SELECT fax FROM inserted),
		phone_ext = (SELECT phone_ext FROM inserted),
		cad_primary = (SELECT cad_primary FROM inserted),
		cad_primary_version = (SELECT cad_primary_version FROM inserted),
		cad_secondary = (SELECT cad_secondary FROM inserted),
		cad_secondary_version = (SELECT cad_secondary_version FROM inserted),
		cad_other = (SELECT cad_other FROM inserted),
		cad_other_version = (SELECT cad_other_version FROM inserted),
		phone_country_code = (SELECT phone_country_code FROM inserted),
		record_state = (SELECT record_state FROM inserted)
	WHERE user_id = (SELECT user_id FROM inserted)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER [usr_smc_Delete] ON dbo.usr_smc
FOR DELETE
AS
DELETE FROM SMC_New_Products.dbo.usr_smc WHERE user_id = (SELECT user_id FROM deleted)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

UPDATE u 
	SET
		firstname = i.firstname,
		lastname = i.lastname
FROM usr_smc u
INNER JOIN inserted i
ON u.user_id = i.user_id

quote:

---

Originally posted by tduggan

firstname = (SELECT firstname FROM inserted)

On each of these is where you are having the problem. There can be more than one row in the inserted table. So you are trying to set a column equal to a bunch of values. You'll need to use a JOIN instead in your UPDATE:

UPDATE u 
	SET
		firstname = i.firstname,
		lastname = i.lastname
FROM usr_smc u
INNER JOIN inserted i
ON u.user_id = i.user_id

Obviously, I didn't write out all of the various SETs.

For your delete trigger, you'll need to change WHERE user_id = ... to WHERE user_id IN ...

Tara

---