Solved

SQLite to MS SQL

Posted on 2014-10-07
6
364 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 46

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 46

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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 46

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

910 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now