Solved

SQLite to MS SQL

Posted on 2014-10-07
6
378 Views
Last Modified: 2014-10-08
Hello,

I have a database that I created in SQLite, and I need to convert it over to MS SQL server environment.  When I execute the attached code there were several errors, which I corrected.  However I am unable to get the equivalent syntax for the attached Trigger as I keep getting the following error message:

"Msg 111, Level 15, State 1, Line 384
'CREATE TRIGGER' must be the first statement in a query batch."

I am not sure how to write this for SQL server, so it would be greatly appreciated if someone correct this line, as there are several triggers that need to be created.

Regards,
aj85
mssql-script.txt
0
Comment
Question by:aj85
  • 3
  • 2
6 Comments
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40367762
Before the CREATE TRIGGER statement, add a GO:
(...)
CREATE INDEX [IX_PersonsByRace] ON [Persons] ([Race])
GO --> ADD this

CREATE TRIGGER [UpdatePhotoTStamp]
AFTER UPDATE OF [Type], [Person], [Booking], [Type], [Location], [Content], [Description], [Officer], [Source], [AgeDays], [AgeYears], [Format]
(...)

Open in new window

0
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 40367767
Was giving a better look on your trigger and doesn't seems to me compatible with T-SQL. I've changed it:
CREATE TRIGGER [UpdatePhotoTStamp]
ON [Photos]
AFTER UPDATE 
AS
	UPDATE Photos SET TStamp=CURRENT_TIMESTAMP
        WHERE Photos.ID=Inserted.ID
GO

Open in new window

0
 
LVL 33

Assisted Solution

by:ste5an
ste5an earned 250 total points
ID: 40367869
Caveat: Triggers in SQL Server are executed per statement, thus the trigger is called once, even if multiple rows are affected.
Also they should use the SET NOCOUNT ON; directive to avoid problems with consumers getting multiple rows affected messages.

CREATE TRIGGER tr_UpdatePhotoTStamp ON Photos
AFTER UPDATE 
AS
  SET NOCOUNT ON;

  UPDATE P
  SET TStamp=CURRENT_TIMESTAMP
  FROM Photos P
      INNER JOIN INSERTED I ON I.ID = P.ID
GO

Open in new window

0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:aj85
ID: 40368362
Hello Vitor,

Thanks for your reply.  So are you saying that I no longer need this line of code for T-SQL:

"AFTER UPDATE AS [Type], [Person], [Booking], [Type], [Location], [Content], [Description], [Officer], [Source], [AgeDays], [AgeYears], [Format]"

That it should now be:

"AFTER UPDATE
AS
      UPDATE Photos SET TStamp=CURRENT_TIMESTAMP
        WHERE Photos.ID=Inserted.ID"

I just want to make sure I have understood the changes you have implemented.

Thanks,
aj85
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40368388
Yes. The code that you have doesn't work in SQL Server.
ste5an's update statement it's the correct one since I forgot the INNER JOIN clause and he also have a point on the SET NOCOUNT ON.
0
 

Author Comment

by:aj85
ID: 40368399
Hello Vitor,

Okay understood, thank you both very much.

Regards,
aj85
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question