asked on

Error

I get the following error while running the attached sql,

Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'TRIGGER'.

i am checking the max value from the info and if it is 1210 i am expecting the following to run,
any ideas , what i am doing wrong.

Trigger:
---------------------

IF (SELECT MAX(Version) FROM info WHERE Version IS NOT NULL) = 1210
BEGIN

      Print 'Creating Trigger XXX_AUDIT....'

            CREATE TRIGGER XXX_AUDIT
            ON Base
            AFTER INSERT, UPDATE, DELETE
            NOT FOR REPLICATION
            AS
            BEGIN
                  DECLARE @Action char(1),
                        @LastChange datetime,
                        @LastChangeUser varchar(12),
                        @Flag varchar(100)

                  SET @LastChange = GETDATE()
                  SET @LastChangeUser = dbo.udf_GETGMSUSER()


            IF EXISTS (SELECT 1 FROM inserted)
            BEGIN
                  IF EXISTS (SELECT 1 FROM deleted)
                        SET @Action = 'U'
                  ELSE
                        SET @Action = 'I'

                  UPDATE Base SET
                  LastChange=@LastChange,LastChangeUser=@LastChangeUser
                  FROM Base b, inserted i
                  WHERE
                  b.Base=i.Base
                  AND b.PriceIndex=i.PriceIndex

            END
            ELSE
                  SET @Action = 'D'


            SET @Flag = (SELECT UPPER(CurrentValue) FROM Globals WHERE Type='History')
            IF @@ERROR <> 0
                  SET @Flag = 'NO'

                  IF @Flag = 'YES'
            BEGIN
                  IF @Action = 'I' OR @Action = 'U'
              INSERT INTO Base
              (Action,Base,PriceIndex,BaseType,Description,Currency,CurrencyUnit,TerminationDate,
              PriceIncrement,ComponentCurrency,ComponentCurrencyUnit,LastChange,LastChangeUser)
                        (SELECT @Action,Base,PriceIndex,BaseType,Description,Currency,CurrencyUnit,TerminationDate,
              PriceIncrement,ComponentCurrency,ComponentCurrencyUnit,@LastChange,@LastChangeUser
                        FROM inserted)
                  ELSE
              INSERT INTO Base
              (Action,Basket,PriceIndex,BaseType,Description,Currency,CurrencyUnit,TerminationDate,
              PriceIncrement,ComponentCurrency,ComponentCurrencyUnit,LastChange,LastChangeUser)
                        (SELECT @Action,Base,PriceIndex,BaseType,Description,Currency,CurrencyUnit,TerminationDate,
              PriceIncrement,ComponentCurrency,ComponentCurrencyUnit,@LastChange,@LastChangeUser
                        FROM deleted)
            END

      insert into info (commenttxt,Version) values       ('XXX', 1211)
END
go

mokule

try something like this

IF (SELECT MAX(Version) FROM info WHERE Version IS NOT NULL) <> 1210
RETURN

Print 'Creating Trigger XXX_AUDIT....'
GO

CREATE TRIGGER XXX_AUDIT
ON Base
.......

crishna1

ASKER

i want to run this when the return value = 1210

mokule

exactly hence RETURN if <>1210

crishna1

ASKER

i am not getting you, from the clause you are using above , the script will not run if the value is not equal to 1210, but it is equal to 1210 in this case and it is give the error mentioned earlier.

mokule

IF (SELECT MAX(Version) FROM info WHERE Version IS NOT NULL) <> 1210
RETURN /* <------------------ If value <> 1210 the rest of the script will not be executed */

/* this will be executed if value = 1210 Is it OK? */
Print 'Creating Trigger XXX_AUDIT....'
GO /* <------------------- this is important */

/* the rest is as You have, it runs perfectly */
CREATE TRIGGER XXX_AUDIT
ON Base

crishna1

ASKER

it still gives me the sam error. i tried adding the GO , deleted the whole print statement , no luck.

mokule

Can You post Your script after changes?

crishna1

ASKER

IF (SELECT MAX(Version) FROM info WHERE Version IS NOT NULL) = 1210
BEGIN
CREATE TRIGGER XXX_AUDIT
ON Base
AFTER INSERT, UPDATE, DELETE
NOT FOR REPLICATION
AS
BEGIN
DECLARE @Action char(1),
@LastChange datetime,
@LastChangeUser varchar(12),
@Flag varchar(100)

SET @LastChange = GETDATE()
SET @LastChangeUser = dbo.udf_GETGMSUSER()

IF EXISTS (SELECT 1 FROM inserted)
BEGIN
IF EXISTS (SELECT 1 FROM deleted)
SET @Action = 'U'
ELSE
SET @Action = 'I'

UPDATE Base SET
LastChange=@LastChange,LastChangeUser=@LastChangeUser
FROM Base b, inserted i
WHERE
b.Base=i.Base
AND b.PriceIndex=i.PriceIndex

END
ELSE
SET @Action = 'D'

SET @Flag = (SELECT UPPER(CurrentValue) FROM Globals WHERE Type='History')
IF @@ERROR <> 0
SET @Flag = 'NO'

IF @Flag = 'YES'
BEGIN
IF @Action = 'I' OR @Action = 'U'
INSERT INTO Base
(Action,Base,PriceIndex,BaseType,Description,Currency,CurrencyUnit,TerminationDate,
PriceIncrement,ComponentCurrency,ComponentCurrencyUnit,LastChange,LastChangeUser)
(SELECT @Action,Base,PriceIndex,BaseType,Description,Currency,CurrencyUnit,TerminationDate,
PriceIncrement,ComponentCurrency,ComponentCurrencyUnit,@LastChange,@LastChangeUser
FROM inserted)
ELSE
INSERT INTO Base
(Action,Basket,PriceIndex,BaseType,Description,Currency,CurrencyUnit,TerminationDate,
PriceIncrement,ComponentCurrency,ComponentCurrencyUnit,LastChange,LastChangeUser)
(SELECT @Action,Base,PriceIndex,BaseType,Description,Currency,CurrencyUnit,TerminationDate,
PriceIncrement,ComponentCurrency,ComponentCurrencyUnit,@LastChange,@LastChangeUser
FROM deleted)
END

insert into info (commenttxt,Version) values ('XXX', 1211)
END
go

crishna1

ASKER

FYI: Error

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'TRIGGER'.

mokule

Sorry but I can't see any changes as I suggest.
Please do as I suggest and it will work

crishna1

ASKER

Like what ? adding a GO after the print statement!

ASKER CERTIFIED SOLUTION

mokule

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mokule

Should say
Remember to change each single quote in Your script to two single quote