Incorrect Syntax

[primagic]

I have the following code in a trigger, however I am getting the error

Msg 156, Level 15, State 1, Procedure AddCertificateNumber, Line 28
Incorrect syntax near the keyword 'SELECT'.

Afer this line

SET @Upper = 9999999 ---- The highest random number

USE [hmtcms]
GO
/****** Object:  Trigger [dbo].[AddCertificateNumber]    Script Date: 05/13/2013 22:32:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[AddCertificateNumber] ON [dbo].[tblCertificates] 
  AFTER INSERT
AS 
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- get the last id value of the record inserted or updated
  DECLARE @id INT
  SELECT @id = [CertificateId]
  FROM INSERTED
  
  ---- Create the variables for the random number generation
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

---- This will create a random number between 1 and 999
SET @Lower = 1000000 ---- The lowest random number
SET @Upper = 9999999 ---- The highest random number

SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random

  -- Insert statements for trigger here
  UPDATE tblCertificates 
  SET tblCertificates.CertificateNumber = SELECT @Random
  WHERE [CertificateId] = @id 

END

 

[lameid]

First, it is very important to understand that triggers do not act on one row at a time but an entire set of changes. It has been a while since I wrote one and I don't have BOL handy on this machine but it looks like you are going to modify only one record.

That being the case, syntax issue....

CODE
USE [hmtcms]
GO
/****** Object:  Trigger [dbo].[AddCertificateNumber]    Script Date: 05/13/2013 22:32:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[AddCertificateNumber] ON [dbo].[tblCertificates] 
  AFTER INSERT
AS 
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- get the last id value of the record inserted or updated
  DECLARE @id INT
  SELECT @id = [CertificateId]
  FROM INSERTED
  
  ---- Create the variables for the random number generation
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

---- This will create a random number between 1 and 999
SET @Lower = 1000000 ---- The lowest random number
SET @Upper = 9999999 ---- The highest random number

[Red]SET[/Red] @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) --[Red]This logic looks odd to me vs. the comment above...[/Red]

--[Red]SELECT @Random[/Red]

  -- Insert statements for trigger here
  UPDATE tblCertificates 
  SET tblCertificates.CertificateNumber = @Random [Red]--SELECT @Random[/Red]
  WHERE [CertificateId] = @id 

END

 

[lameid]

I just want to reiterate I only fixed the code from blowing up. It still is probably a bad idea to have a trigger this way that does not address all records inserted (more than one record at a time). Additionally, it would seem that you could insert your random number versus using a trigger when the data goes in. A trigger seems like overkill.