SQLite to MS SQL

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
aj85Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aj85Author Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
aj85Author Commented:
Hello Vitor,

Okay understood, thank you both very much.

Regards,
aj85
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.