Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

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
0
crishna1
Asked:
crishna1
  • 7
  • 6
1 Solution
 
mokuleCommented:
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
.......
0
 
crishna1Author Commented:
i want to run this when the return value = 1210
0
 
mokuleCommented:
exactly hence RETURN if <>1210
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
crishna1Author Commented:
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.
0
 
mokuleCommented:
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
0
 
crishna1Author Commented:
it still gives me the sam error. i tried adding the GO , deleted the whole print statement , no luck.
0
 
mokuleCommented:
Can You post Your script after changes?
0
 
crishna1Author Commented:
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
0
 
crishna1Author Commented:
FYI: Error

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'TRIGGER'.
0
 
mokuleCommented:
Sorry but I can't see any changes as I suggest.
Please do as I suggest and it will work
0
 
crishna1Author Commented:
Like what ? adding a GO after the print statement!
0
 
mokuleCommented:
You can do it also like this

IF (SELECT MAX(Version) FROM info WHERE Version IS NOT NULL) = 1210
BEGIN
         EXEC('CREATE TRIGGER XXX_AUDIT......')
END

Remember to change each comma in Your script to two commas
 
0
 
mokuleCommented:
Should say
Remember to change each single quote in Your script to two single quote
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now