Solved

SQLite to MS SQL

Posted on 2014-10-07
6
374 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 47

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 47

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 47

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 46
what are the unique tables in SQL master database 5 62
Query group by data in SQL Server - cursor? 3 34
Merging spreadsheets 8 42
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.

831 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