Solved

SQLite to MS SQL

Posted on 2014-10-07
6
384 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 49

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 49

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
Technology Partners: 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!

 

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 49

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

730 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