Incorrect syntax near 'END'. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Incorrect syntax near 'END'.

Author

Topic

jak3f
Starting Member

33 Posts

Posted - 2009-09-24 : 12:01:11

Creating this trigger in vs2008 using sql server 2005,
I am not sure what is wrong with it, anyone help?

ALTER TRIGGER tri_Customers
ON dbo.Customers
INSTEAD OF Insert
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustomerID int;
DECLARE @Name varchar(max);
DECLARE @Address varchar(max);
DECLARE @City varchar(max);
DECLARE @State varchar(max);
DECLARE @ZipCode varchar(max);
DECLARE @Phone varchar(max);
DECLARE @Email varchar(max);
DECLARE @EFIN varchar(max);
DECLARE @RegCode varchar(max);
DECLARE @AccountNumber varchar(max);
DECLARE @ChargeNumber varchar(max);
DECLARE @ProductCode varchar(max);
DECLARE @PurchaseDate varchar(max);
DECLARE @PriorSoftware varchar(max);
DECLARE @Bank varchar(max);
DECLARE @Renewed varchar(max);
DECLARE @BalanceDue varchar(max);
DECLARE @result int;

DECLARE trg_ins_customers CURSOR FOR
SELECT * FROM inserted;
OPEN trg_ins_customers;

FETCH NEXT FROM trg_ins_customers
INTO @CustomerID, @Name, @Address, @City, @State, @ZipCode, @Phone, @Email, @EFIN, @RegCode,
@AccountNumber, @ChargeNumber, @ProductCode, @PurchaseDate, @PriorSoftware, @Bank, @Renewed, @BalanceDue;

WHILE @@Fetch_status = 0
BEGIN
-- find out if there is a row now
SET @result = (SELECT count(*) from Customers
WHERE CustomerID = @CustomerID)

IF @result = 1
BEGIN
--since theres already a row, do update
UPDATE Customers
SET Name = @Name, Address = @Address, City = @City, State = @State, ZipCode = @ZipCode, Phone = @Phone, Email = @Email,
EFIN = @EFIN, RegCode = @RegCode, AccountNumber = @AccountNumber, ChargeNumber = @ChargeNumber, ProductCode = @ProductCode,
PurchaseDate = @PurchaseDate, PriorSoftware = @PriorSoftware, Bank = @Bank, Renewed = @Renewed, BalanceDue = @BalanceDue
WHERE CustomerID = @CustomerID;
END
ELSE
BEGIN
--where there is no row, insert
INSERT INTO Customers
(CustomerID, Name, Address, City, State, ZipCode, Phone, Email, EFIN, RegCode, AccountNumber, ChargeNumber,
ProductCode, PurchaseDate, PriorSoftware, Bank, Renewed, BalanceDue)
VALUES
(@CustomerID, @Name, @Address, @City, @State, @ZipCode, @Phone, @Email, @EFIN, @RegCode,
@AccountNumber, @ChargeNumber, @ProductCode, @PurchaseDate, @PriorSoftware, @Bank, @Renewed, @BalanceDue)
--UPDATE Customers

--Pull the next row
FETCH NEXT FROM trg_ins_customers
INTO @CustomerID, @Name, @Address, @City, @State, @ZipCode, @Phone, @Email, @EFIN, @RegCode,
@AccountNumber, @ChargeNumber, @ProductCode, @PurchaseDate, @PriorSoftware, @Bank, @Renewed, @BalanceDue;

END --Cursor

CLOSE trg_ins_customers;
DEALLOCATE trg_ins_customers;
END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-24 : 13:14:38

You are missing and END. I cut out all the "fluff" so you can see where there a BEGIN without and END:

ALTER TRIGGER tri_Customers
ON dbo.Customers
INSTEAD OF Insert
AS
BEGIN
	WHILE @@Fetch_status = 0
	BEGIN

		IF @result = 1 
		BEGIN
		END
		ELSE
		BEGIN
		END -- Missing
	END --Cursor 
END

jak3f
Starting Member

33 Posts

Posted - 2009-09-24 : 13:45:31

Ok great that worked thanks. I am getting the impression that this is not going to work for updating every row. How can I set it to update all rows?

jak3f
Starting Member

33 Posts

Posted - 2009-09-24 : 13:45:32

Ok great that worked thanks. I am getting the impression that this is not going to work for updating every row. How can I set it to update all rows?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-24 : 13:48:30

"fluff" was kind

jak3f, get rid of the cursor and variables. Do it this way:


       --UPDATE where customers already exist
       update c set
              c.Name = i.name
              ,c.Address = i.address
              ,etc...
       from   inserted i
       inner join customers c
              on c.customerid = i.customerid

       --Insert NEW customers
       INSERT INTO Customers
              (CustomerID
              ,[Name]
              ,[Address]
              ,etc...)
       select CustomerID
              ,i.[Name]
              ,i.[Address]
              ,ect...
       from   inserted i
       left outer join Customers c
              on c.customerid = i.customerid
       where  c.customerid is null

Be One with the Optimizer
TG

jak3f
Starting Member

33 Posts

Posted - 2009-09-24 : 13:51:44

Haha alright buddy im on it thanks!
One thing though, I have IGNORE DUP KEY = YES, so I am afraid on the insert it is ignoring the DUP key all together and not even going to update it.. is that true?

TG thanks for the input im fixing it now

jak3f
Starting Member

33 Posts

Posted - 2009-09-24 : 13:58:45

Ok, I tried it, and it ran fine, but the values werent updated, and I think that def has to do with Ignore duplicate key set to yes, but if i set that to no then i cannot add anything I get the Duplicate PK error

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-24 : 14:21:37

inserts works for me for both existing and non-existing primary key values:


use tempdb

go
create table junk 
       (pk int not null
       ,v1 int
       ,v2 int
       ,primary key clustered (pk)with (ignore_dup_key = ON))
go

create trigger tr_junk on junk instead of insert
as
begin
       insert junk
       select i.pk, i.v1, i.v2
       from inserted i
       left outer join junk j on j.pk = i.pk
       where j.pk is null

       update j set
              j.v1 = i.v1
              ,j.v2 = i.v2
       from   inserted i
       inner join junk j on j.pk = i.pk
end
go

insert junk select 1,1,1
insert junk select 2,1,1
insert junk select 3,1,1
insert junk select 1,1,9999

select * from junk

go
drop table junk

OUTPUT:
pk          v1          v2
----------- ----------- -----------
1           1           9999
2           1           1
3           1           1

Be One with the Optimizer
TG

jak3f
Starting Member

33 Posts

Posted - 2009-09-24 : 15:23:28

Great thanks for the example. I followed urs exactly to the T and I have one problem still..
Heres my methodology:
I am importing an excel spreadsheet with SqlBulkCopy in VS2008, so my inserted i are excel spreadsheet values. I am thinking that since its an sqlbulkcopy its throwing off my triggers, since yours obviously works, and mine will insert new records but not update current ones.

I think it may have something to do with that. but im not exaclty certain, heres what I have so far tho.
ALTER TRIGGER trg_duplicate_prevent
ON Customers
INSTEAD OF Insert
AS
BEGIN
UPDATE C SET
C.Name = i.Name
,C.Address = i.Address
,C.City = i.City
,C.State = i.State
,C.ZipCode = i.ZipCode
,C.Phone = i.Phone
,C.Email = i.Email
,C.EFIN = i.EFIN
,C.RegCode = i.RegCode
,C.AccountNumber = i.AccountNumber
,C.ChargeNumber = i.ChargeNumber
,C.ProductCode = i.ProductCode
,C.PurchaseDate = i.PurchaseDate
,C.PriorSoftware = i.PriorSoftware
,C.Bank = i.Bank
,C.BalanceDue = i.BalanceDue
,C.Renewed = i.Renewed
FROM inserted i
INNER JOIN Customers C
on C.CustomerID = i.CustomerID

Insert Customers SELECT i.CustomerID,
i.Name,
i.Address,
i.City,
i.State,
i.ZipCode,
i.Phone,
i.Email,
i.EFIN,
i.RegCode,
i.AccountNumber,
i.ChargeNumber,
i.ProductCode,
i.PurchaseDate,
i.PriorSoftware,
i.Bank,
i.BalanceDue,
i.Renewed
FROM inserted i
left outer join Customers c
on c.CustomerID = i.CustomerID
WHERE c.CustomerID is null
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-24 : 15:44:21

from: Using bcp and BULK INSERT

quote:

All bulk copy operations (the BULK INSERT statement, bcp utility, and the bulk copy API) support a bulk copy hint, FIRE_TRIGGERS. If FIRE_TRIGGERS is specified on a bulk copy operation that is copying rows into a table, INSERT and INSTEAD OF triggers defined on the destination table are executed for all rows inserted by the bulk copy operation. By default, bulk copy operations do not execute triggers.

Be One with the Optimizer
TG

jak3f
Starting Member

33 Posts

Posted - 2009-09-24 : 15:46:28

YAYAYAYAYAY i figured it out..
I had to add a line of code allowing my bulkcopyclass to fire the triggers, who knows I may have been right this whole time cuz I have written 10 dif triggers to get this to work.

jak3f
Starting Member

33 Posts

Posted - 2009-09-24 : 15:47:01

TG thanks so much for all your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources