|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
understanding what trigger is doingI ran this update statement "Update tblCompanyContacts SET varCompContactPhone =REPLACE(varCompContactPhone,'abcdefghijklmnopqrstuvwxyz()-_+=!@#$%^&*,.?/:;','') " and it fires off this trigger CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts] FOR INSERT, UPDATE AS IF (Select bitCompPrimaryContact From Inserted) = 1 Begin Update tblCompanyContacts Set bitCompPrimaryContact = 0 Where numCompanyId = (Select numCompanyId From Inserted) And numContactID <> (Select numContactId From Inserted) End I get this error on the update statement. "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." I think the trigger is trying to reset the primary company contact. So, I guess the trigger is trying to update more than one row. Is this not allowed? When I ran the original update statement, is the trigger firing for each row that is updated? Here's the table CREATE TABLE [tblCompanyContacts] ( [numContactId] [numeric](18, 0) NOT NULL , [numCompanyId] [numeric](18, 0) NOT NULL , [varCompContactFirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [varCompContactLastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [varCompContactAddress1] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [varCompContactAddress2] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [varCompContactCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [numStateId] [numeric](18, 0) NULL , [varCompContactZip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [varCompContactPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [varCompContactExtension] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [varCompContactMobile] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [varCompContactFax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [varCompContactEmail] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [varCompContactTitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [numRoleId] [int] NULL , [bitCompPrimaryContact] [bit] NULL , [intCompContactStatus] [int] NULL , [dtCompContactCreationDate] [datetime] NULL , [dtCompContactLastModifiedDate] [datetime] NULL ) ON [PRIMARY] GO Is there a better way to do this? Thanks, -- Dan D. change this part to
numContactID <> (Select numContactId From Inserted) numContactID not in (Select numContactId From Inserted) Hope this helps. Sorry also change this
numCompanyId = (Select numCompanyId From Inserted) to numCompanyId in (Select numCompanyId From Inserted) Isn't that still going to result in multiple rows being returned. Is it a
problem to have multiple rows returned in a trigger? Thanks, -- Show quoteDan D. "Omnibuzz" wrote: > Sorry also change this > > numCompanyId = > (Select numCompanyId From Inserted) > > to > > numCompanyId in > (Select numCompanyId From Inserted) Hi Dan,
If there are multiple rows returned. Multiple rows will be updated. But there shouldn't be an error thrown. This may create problems if the same contact can exist for multiple
companies. I'm not sure if this would happen, but possibly a consultant working part time for two companies, listed as a contact for both? It seems unlikely, and is irrelevent unless the primaryContact flag is actually beign changed, but seems logically different from what the trigger appears to be attempting. Show quote "Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message news:CE68746A-C898-4E7C-B512-85EB5A102664@microsoft.com... > Sorry also change this > > numCompanyId = > (Select numCompanyId From Inserted) > > to > > numCompanyId in > (Select numCompanyId From Inserted) Good point Jim.
Tried to give a workable solution, But didn't give an attempt to understand the functionality... Well, was working late yesterday The trigger is processed for all rows in an update/insert/delete statement
as a set, rather than one by one. Any logic in the trigger needs to process every row that is modified. Your subselects will potentially select more than one row each, which will cause an error. I think what you want is something like this... The exists checks that the company is the same one accessed in the update/insert statement and the contact is different. This assumes that you want only one primary contact per company, and that numCompanyId and numContactID make up your primary key, or at least have a unique index. If either of these is not the case, then you will have to adjust the logic. CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts] FOR INSERT, UPDATE AS Update tblCompanyContacts Set bitCompPrimaryContact = 0 Where exists( select 1 from inserted where inserted.numCompanyId = tblCompanyContacts.numCompanyId and inserted.numContactId <> tblCompanyContacts.numContactID and inserted.bitCompPrimaryContact = 1 ) go "Dan D." <D***@discussions.microsoft.com> wrote in message =REPLACE(varCompContactPhone,'abcdefghijklmnopqrstuvwxyz()-_+=!@#$%^&*,.?/:;news:97CBF9FD-DD2B-4A1B-B008-32985861BDC2@microsoft.com... > Using SS2000 SP4. > I ran this update statement "Update tblCompanyContacts SET > varCompContactPhone > ','') " Show quote > and it fires off this trigger > CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts] > FOR INSERT, UPDATE > AS > IF (Select bitCompPrimaryContact From Inserted) = 1 > Begin > Update tblCompanyContacts Set bitCompPrimaryContact = 0 Where numCompanyId = > (Select numCompanyId From Inserted) And numContactID <> (Select numContactId > From Inserted) > End > I get this error on the update statement. "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." > > I think the trigger is trying to reset the primary company contact. So, I > guess the trigger is trying to update more than one row. Is this not allowed? > When I ran the original update statement, is the trigger firing for each row > that is updated? > > Here's the table > CREATE TABLE [tblCompanyContacts] ( > [numContactId] [numeric](18, 0) NOT NULL , > [numCompanyId] [numeric](18, 0) NOT NULL , > [varCompContactFirstName] [nvarchar] (50) COLLATE > SQL_Latin1_General_CP1_CI_AS NOT NULL , > [varCompContactLastName] [nvarchar] (50) COLLATE > SQL_Latin1_General_CP1_CI_AS NOT NULL , > [varCompContactAddress1] [nvarchar] (100) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL , > [varCompContactAddress2] [nvarchar] (100) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL , > [varCompContactCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [numStateId] [numeric](18, 0) NULL , > [varCompContactZip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [varCompContactPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [varCompContactExtension] [varchar] (25) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL , > [varCompContactMobile] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [varCompContactFax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [varCompContactEmail] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [varCompContactTitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [numRoleId] [int] NULL , > [bitCompPrimaryContact] [bit] NULL , > [intCompContactStatus] [int] NULL , > [dtCompContactCreationDate] [datetime] NULL , > [dtCompContactLastModifiedDate] [datetime] NULL > ) ON [PRIMARY] > GO > > Is there a better way to do this? > > Thanks, > -- > Dan D. That worked on a single insert but I'm not sure that I understand why. If
there are two rows in the table with the same numCompanyId and different numContactId and I insert a third row, it still has to update more than one. How is it doing it differently than the original trigger. We also have a process that runs overnight. The process gets a file from the client and replaces the tblCompanyContacts table. I'll test it on this, too. Thanks, -- Show quoteDan D. "Jim Underwood" wrote: > The trigger is processed for all rows in an update/insert/delete statement > as a set, rather than one by one. Any logic in the trigger needs to process > every row that is modified. Your subselects will potentially select more > than one row each, which will cause an error. I think what you want is > something like this... > > The exists checks that the company is the same one accessed in the > update/insert statement and the contact is different. This assumes that you > want only one primary contact per company, and that numCompanyId and > numContactID make up your primary key, or at least have a unique index. If > either of these is not the case, then you will have to adjust the logic. > > CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts] > FOR INSERT, UPDATE > AS > Update tblCompanyContacts > Set bitCompPrimaryContact = 0 > Where exists( > select 1 > from inserted > where inserted.numCompanyId = tblCompanyContacts.numCompanyId > and inserted.numContactId <> tblCompanyContacts.numContactID > and inserted.bitCompPrimaryContact = 1 > ) > go > > "Dan D." <D***@discussions.microsoft.com> wrote in message > news:97CBF9FD-DD2B-4A1B-B008-32985861BDC2@microsoft.com... > > Using SS2000 SP4. > > I ran this update statement "Update tblCompanyContacts SET > > varCompContactPhone > > > =REPLACE(varCompContactPhone,'abcdefghijklmnopqrstuvwxyz()-_+=!@#$%^&*,.?/:; > ','') " > > and it fires off this trigger > > CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts] > > FOR INSERT, UPDATE > > AS > > IF (Select bitCompPrimaryContact From Inserted) = 1 > > Begin > > Update tblCompanyContacts Set bitCompPrimaryContact = 0 Where numCompanyId > = > > (Select numCompanyId From Inserted) And numContactID <> (Select > numContactId > > From Inserted) > > End > > I get this error on the update statement. "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." > > > > I think the trigger is trying to reset the primary company contact. So, I > > guess the trigger is trying to update more than one row. Is this not > allowed? > > When I ran the original update statement, is the trigger firing for each > row > > that is updated? > > > > Here's the table > > CREATE TABLE [tblCompanyContacts] ( > > [numContactId] [numeric](18, 0) NOT NULL , > > [numCompanyId] [numeric](18, 0) NOT NULL , > > [varCompContactFirstName] [nvarchar] (50) COLLATE > > SQL_Latin1_General_CP1_CI_AS NOT NULL , > > [varCompContactLastName] [nvarchar] (50) COLLATE > > SQL_Latin1_General_CP1_CI_AS NOT NULL , > > [varCompContactAddress1] [nvarchar] (100) COLLATE > > SQL_Latin1_General_CP1_CI_AS NULL , > > [varCompContactAddress2] [nvarchar] (100) COLLATE > > SQL_Latin1_General_CP1_CI_AS NULL , > > [varCompContactCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > > NULL , > > [numStateId] [numeric](18, 0) NULL , > > [varCompContactZip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > > NULL , > > [varCompContactPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > > NULL , > > [varCompContactExtension] [varchar] (25) COLLATE > > SQL_Latin1_General_CP1_CI_AS NULL , > > [varCompContactMobile] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS > > NULL , > > [varCompContactFax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS > > NULL , > > [varCompContactEmail] [nvarchar] (100) COLLATE > SQL_Latin1_General_CP1_CI_AS > > NULL , > > [varCompContactTitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > > NULL , > > [numRoleId] [int] NULL , > > [bitCompPrimaryContact] [bit] NULL , > > [intCompContactStatus] [int] NULL , > > [dtCompContactCreationDate] [datetime] NULL , > > [dtCompContactLastModifiedDate] [datetime] NULL > > ) ON [PRIMARY] > > GO > > > > Is there a better way to do this? > > > > Thanks, > > -- > > Dan D. > > > If you have the following data:
Company, Contact, PrimaryContact 12,5,0 12,6,1 12,7,0 and you update company 12, contact 7 to be the Primary contact. Then table inserted will contain one row with the updated column values. (12,7,1). The end result will be a SQL statement that looks like this. Update tblCompanyContacts set bitCompPrimaryContact = 0 Where exists( select 1 from inserted where inserted.numCompanyId [12] = tblCompanyContacts.numCompanyId and inserted.numContactId [7] <> tblCompanyContacts.numContactID and inserted.bitCompPrimaryContact [1] = 1 ) So, all rows where company = 12, and contact <> 7 will be updated (only when the new value for primary contact is set to 1). This would update both of your rows for contact 5 and 6 with a primary contact flag of 0. You could add another criteria to the where clause to make sure only rows that actually need to be updated get updated. Meaning don't set bitCompPrimaryContact = 0 if ti is already 0. Show quote "Dan D." <D***@discussions.microsoft.com> wrote in message =REPLACE(varCompContactPhone,'abcdefghijklmnopqrstuvwxyz()-_+=!@#$%^&*,.?/:;news:54FBE88E-5EE7-4B35-9D7F-E28AB5DF4793@microsoft.com... > That worked on a single insert but I'm not sure that I understand why. If > there are two rows in the table with the same numCompanyId and different > numContactId and I insert a third row, it still has to update more than one. > How is it doing it differently than the original trigger. > > We also have a process that runs overnight. The process gets a file from the > client and replaces the tblCompanyContacts table. I'll test it on this, too. > > Thanks, > -- > Dan D. > > > "Jim Underwood" wrote: > > > The trigger is processed for all rows in an update/insert/delete statement > > as a set, rather than one by one. Any logic in the trigger needs to process > > every row that is modified. Your subselects will potentially select more > > than one row each, which will cause an error. I think what you want is > > something like this... > > > > The exists checks that the company is the same one accessed in the > > update/insert statement and the contact is different. This assumes that you > > want only one primary contact per company, and that numCompanyId and > > numContactID make up your primary key, or at least have a unique index. If > > either of these is not the case, then you will have to adjust the logic. > > > > CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts] > > FOR INSERT, UPDATE > > AS > > Update tblCompanyContacts > > Set bitCompPrimaryContact = 0 > > Where exists( > > select 1 > > from inserted > > where inserted.numCompanyId = tblCompanyContacts.numCompanyId > > and inserted.numContactId <> tblCompanyContacts.numContactID > > and inserted.bitCompPrimaryContact = 1 > > ) > > go > > > > "Dan D." <D***@discussions.microsoft.com> wrote in message > > news:97CBF9FD-DD2B-4A1B-B008-32985861BDC2@microsoft.com... > > > Using SS2000 SP4. > > > I ran this update statement "Update tblCompanyContacts SET > > > varCompContactPhone > > > > > Show quote > > ','') " > > > and it fires off this trigger > > > CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts] > > > FOR INSERT, UPDATE > > > AS > > > IF (Select bitCompPrimaryContact From Inserted) = 1 > > > Begin > > > Update tblCompanyContacts Set bitCompPrimaryContact = 0 Where numCompanyId > > = > > > (Select numCompanyId From Inserted) And numContactID <> (Select > > numContactId > > > From Inserted) > > > End > > > I get this error on the update statement. "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." > > > > > > I think the trigger is trying to reset the primary company contact. So, I > > > guess the trigger is trying to update more than one row. Is this not > > allowed? > > > When I ran the original update statement, is the trigger firing for each > > row > > > that is updated? > > > > > > Here's the table > > > CREATE TABLE [tblCompanyContacts] ( > > > [numContactId] [numeric](18, 0) NOT NULL , > > > [numCompanyId] [numeric](18, 0) NOT NULL , > > > [varCompContactFirstName] [nvarchar] (50) COLLATE > > > SQL_Latin1_General_CP1_CI_AS NOT NULL , > > > [varCompContactLastName] [nvarchar] (50) COLLATE > > > SQL_Latin1_General_CP1_CI_AS NOT NULL , > > > [varCompContactAddress1] [nvarchar] (100) COLLATE > > > SQL_Latin1_General_CP1_CI_AS NULL , > > > [varCompContactAddress2] [nvarchar] (100) COLLATE > > > SQL_Latin1_General_CP1_CI_AS NULL , > > > [varCompContactCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > > > NULL , > > > [numStateId] [numeric](18, 0) NULL , > > > [varCompContactZip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > > > NULL , > > > [varCompContactPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > > > NULL , > > > [varCompContactExtension] [varchar] (25) COLLATE > > > SQL_Latin1_General_CP1_CI_AS NULL , > > > [varCompContactMobile] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS > > > NULL , > > > [varCompContactFax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS > > > NULL , > > > [varCompContactEmail] [nvarchar] (100) COLLATE > > SQL_Latin1_General_CP1_CI_AS > > > NULL , > > > [varCompContactTitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > > > NULL , > > > [numRoleId] [int] NULL , > > > [bitCompPrimaryContact] [bit] NULL , > > > [intCompContactStatus] [int] NULL , > > > [dtCompContactCreationDate] [datetime] NULL , > > > [dtCompContactLastModifiedDate] [datetime] NULL > > > ) ON [PRIMARY] > > > GO > > > > > > Is there a better way to do this? > > > > > > Thanks, > > > -- > > > Dan D. > > > > > > I understand. Your subquery is only returning the one row. Thanks.
-- Show quoteDan D. "Jim Underwood" wrote: > If you have the following data: > > Company, Contact, PrimaryContact > 12,5,0 > 12,6,1 > 12,7,0 > > and you update company 12, contact 7 to be the Primary contact. Then table > inserted will contain one row with the updated column values. (12,7,1). The > end result will be a SQL statement that looks like this. > > Update tblCompanyContacts > set bitCompPrimaryContact = 0 > Where exists( > select 1 > from inserted > where inserted.numCompanyId [12] = tblCompanyContacts.numCompanyId > and inserted.numContactId [7] <> tblCompanyContacts.numContactID > and inserted.bitCompPrimaryContact [1] = 1 > ) > > So, all rows where company = 12, and contact <> 7 will be updated (only when > the new value for primary contact is set to 1). > This would update both of your rows for contact 5 and 6 with a primary > contact flag of 0. > > You could add another criteria to the where clause to make sure only rows > that actually need to be updated get updated. Meaning don't set > bitCompPrimaryContact = 0 if ti is already 0. > > > > "Dan D." <D***@discussions.microsoft.com> wrote in message > news:54FBE88E-5EE7-4B35-9D7F-E28AB5DF4793@microsoft.com... > > That worked on a single insert but I'm not sure that I understand why. If > > there are two rows in the table with the same numCompanyId and different > > numContactId and I insert a third row, it still has to update more than > one. > > How is it doing it differently than the original trigger. > > > > We also have a process that runs overnight. The process gets a file from > the > > client and replaces the tblCompanyContacts table. I'll test it on this, > too. > > > > Thanks, > > -- > > Dan D. > > > > > > "Jim Underwood" wrote: > > > > > The trigger is processed for all rows in an update/insert/delete > statement > > > as a set, rather than one by one. Any logic in the trigger needs to > process > > > every row that is modified. Your subselects will potentially select > more > > > than one row each, which will cause an error. I think what you want is > > > something like this... > > > > > > The exists checks that the company is the same one accessed in the > > > update/insert statement and the contact is different. This assumes that > you > > > want only one primary contact per company, and that numCompanyId and > > > numContactID make up your primary key, or at least have a unique index. > If > > > either of these is not the case, then you will have to adjust the logic. > > > > > > CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts] > > > FOR INSERT, UPDATE > > > AS > > > Update tblCompanyContacts > > > Set bitCompPrimaryContact = 0 > > > Where exists( > > > select 1 > > > from inserted > > > where inserted.numCompanyId = tblCompanyContacts.numCompanyId > > > and inserted.numContactId <> tblCompanyContacts.numContactID > > > and inserted.bitCompPrimaryContact = 1 > > > ) > > > go > > > > > > "Dan D." <D***@discussions.microsoft.com> wrote in message > > > news:97CBF9FD-DD2B-4A1B-B008-32985861BDC2@microsoft.com... > > > > Using SS2000 SP4. > > > > I ran this update statement "Update tblCompanyContacts SET > > > > varCompContactPhone > > > > > > > > =REPLACE(varCompContactPhone,'abcdefghijklmnopqrstuvwxyz()-_+=!@#$%^&*,.?/:; > > > ','') " > > > > and it fires off this trigger > > > > CREATE TRIGGER [SetPrimaryContact_Company] ON > [dbo].[tblCompanyContacts] > > > > FOR INSERT, UPDATE > > > > AS > > > > IF (Select bitCompPrimaryContact From Inserted) = 1 > > > > Begin > > > > Update tblCompanyContacts Set bitCompPrimaryContact = 0 Where > numCompanyId > > > = > > > > (Select numCompanyId From Inserted) And numContactID <> (Select > > > numContactId > > > > From Inserted) > > > > End > > > > I get this error on the update statement. "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." > > > > > > > > I think the trigger is trying to reset the primary company contact. > So, I > > > > guess the trigger is trying to update more than one row. Is this not > > > allowed? > > > > When I ran the original update statement, is the trigger firing for > each > > > row > > > > that is updated? > > > > > > > > Here's the table > > > > CREATE TABLE [tblCompanyContacts] ( > > > > [numContactId] [numeric](18, 0) NOT NULL , > > > > [numCompanyId] [numeric](18, 0) NOT NULL , > > > > [varCompContactFirstName] [nvarchar] (50) COLLATE > > > > SQL_Latin1_General_CP1_CI_AS NOT NULL , > > > > [varCompContactLastName] [nvarchar] (50) COLLATE > > > > SQL_Latin1_General_CP1_CI_AS NOT NULL , > > > > [varCompContactAddress1] [nvarchar] (100) COLLATE > > > > SQL_Latin1_General_CP1_CI_AS NULL , > > > > [varCompContactAddress2] [nvarchar] (100) COLLATE > > > > SQL_Latin1_General_CP1_CI_AS NULL , > > > > [varCompContactCity] [nvarchar] (50) COLLATE > SQL_Latin1_General_CP1_CI_AS > > > > NULL , > > > > [numStateId] [numeric](18, 0) NULL , > > > > [varCompContactZip] [varchar] (50) COLLATE > SQL_Latin1_General_CP1_CI_AS > > > > NULL , > > > > [varCompContactPhone] [nvarchar] (50) COLLATE > SQL_Latin1_General_CP1_CI_AS > > > > NULL , > > > > [varCompContactExtension] [varchar] (25) COLLATE > > > > SQL_Latin1_General_CP1_CI_AS NULL , > > > > [varCompContactMobile] [varchar] (25) COLLATE > SQL_Latin1_General_CP1_CI_AS > > > > NULL , > > > > [varCompContactFax] [varchar] (25) COLLATE > SQL_Latin1_General_CP1_CI_AS > > > > NULL , > > > > [varCompContactEmail] [nvarchar] (100) COLLATE > > > SQL_Latin1_General_CP1_CI_AS > > > > NULL , > > > > [varCompContactTitle] [varchar] (50) COLLATE > SQL_Latin1_General_CP1_CI_AS > > > > NULL , > > > > [numRoleId] [int] NULL , > > > > [bitCompPrimaryContact] [bit] NULL , > > > > [intCompContactStatus] [int] NULL , > > > > [dtCompContactCreationDate] [datetime] NULL , > > > > [dtCompContactLastModifiedDate] [datetime] NULL > > > > ) ON [PRIMARY] > > > > GO > > > > > > > > Is there a better way to do this? > > > > > > > > Thanks, > > > > -- > > > > Dan D. > > > > > > > > > > > > I hit some key (alt-Enter?) and submitted early... The criteria that you
can add to insure you are not updating rows that dont need it is: and tblCompanyContacts.bitCompPrimaryContact = 1 Or, you could use and tblCompanyContacts.bitCompPrimaryContact <> 0 Show quote "Dan D." <D***@discussions.microsoft.com> wrote in message =REPLACE(varCompContactPhone,'abcdefghijklmnopqrstuvwxyz()-_+=!@#$%^&*,.?/:;news:54FBE88E-5EE7-4B35-9D7F-E28AB5DF4793@microsoft.com... > That worked on a single insert but I'm not sure that I understand why. If > there are two rows in the table with the same numCompanyId and different > numContactId and I insert a third row, it still has to update more than one. > How is it doing it differently than the original trigger. > > We also have a process that runs overnight. The process gets a file from the > client and replaces the tblCompanyContacts table. I'll test it on this, too. > > Thanks, > -- > Dan D. > > > "Jim Underwood" wrote: > > > The trigger is processed for all rows in an update/insert/delete statement > > as a set, rather than one by one. Any logic in the trigger needs to process > > every row that is modified. Your subselects will potentially select more > > than one row each, which will cause an error. I think what you want is > > something like this... > > > > The exists checks that the company is the same one accessed in the > > update/insert statement and the contact is different. This assumes that you > > want only one primary contact per company, and that numCompanyId and > > numContactID make up your primary key, or at least have a unique index. If > > either of these is not the case, then you will have to adjust the logic. > > > > CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts] > > FOR INSERT, UPDATE > > AS > > Update tblCompanyContacts > > Set bitCompPrimaryContact = 0 > > Where exists( > > select 1 > > from inserted > > where inserted.numCompanyId = tblCompanyContacts.numCompanyId > > and inserted.numContactId <> tblCompanyContacts.numContactID > > and inserted.bitCompPrimaryContact = 1 > > ) > > go > > > > "Dan D." <D***@discussions.microsoft.com> wrote in message > > news:97CBF9FD-DD2B-4A1B-B008-32985861BDC2@microsoft.com... > > > Using SS2000 SP4. > > > I ran this update statement "Update tblCompanyContacts SET > > > varCompContactPhone > > > > > Show quote > > ','') " > > > and it fires off this trigger > > > CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts] > > > FOR INSERT, UPDATE > > > AS > > > IF (Select bitCompPrimaryContact From Inserted) = 1 > > > Begin > > > Update tblCompanyContacts Set bitCompPrimaryContact = 0 Where numCompanyId > > = > > > (Select numCompanyId From Inserted) And numContactID <> (Select > > numContactId > > > From Inserted) > > > End > > > I get this error on the update statement. "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." > > > > > > I think the trigger is trying to reset the primary company contact. So, I > > > guess the trigger is trying to update more than one row. Is this not > > allowed? > > > When I ran the original update statement, is the trigger firing for each > > row > > > that is updated? > > > > > > Here's the table > > > CREATE TABLE [tblCompanyContacts] ( > > > [numContactId] [numeric](18, 0) NOT NULL , > > > [numCompanyId] [numeric](18, 0) NOT NULL , > > > [varCompContactFirstName] [nvarchar] (50) COLLATE > > > SQL_Latin1_General_CP1_CI_AS NOT NULL , > > > [varCompContactLastName] [nvarchar] (50) COLLATE > > > SQL_Latin1_General_CP1_CI_AS NOT NULL , > > > [varCompContactAddress1] [nvarchar] (100) COLLATE > > > SQL_Latin1_General_CP1_CI_AS NULL , > > > [varCompContactAddress2] [nvarchar] (100) COLLATE > > > SQL_Latin1_General_CP1_CI_AS NULL , > > > [varCompContactCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > > > NULL , > > > [numStateId] [numeric](18, 0) NULL , > > > [varCompContactZip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > > > NULL , > > > [varCompContactPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > > > NULL , > > > [varCompContactExtension] [varchar] (25) COLLATE > > > SQL_Latin1_General_CP1_CI_AS NULL , > > > [varCompContactMobile] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS > > > NULL , > > > [varCompContactFax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS > > > NULL , > > > [varCompContactEmail] [nvarchar] (100) COLLATE > > SQL_Latin1_General_CP1_CI_AS > > > NULL , > > > [varCompContactTitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > > > NULL , > > > [numRoleId] [int] NULL , > > > [bitCompPrimaryContact] [bit] NULL , > > > [intCompContactStatus] [int] NULL , > > > [dtCompContactCreationDate] [datetime] NULL , > > > [dtCompContactLastModifiedDate] [datetime] NULL > > > ) ON [PRIMARY] > > > GO > > > > > > Is there a better way to do this? > > > > > > Thanks, > > > -- > > > Dan D. > > > > > >  |
|||||||||||||||||||||||
Other interesting topics