Improve company productivity with a Business Account.Sign Up

x
?
Solved

Trigger in SqlLite

Posted on 2011-03-07
6
Medium Priority
?
424 Views
Last Modified: 2012-05-11
I try to update the DateModified field of the updated record in sql lite but I can't find the syntex to do so.
I tried this one that works for MSSql:
CREATE TRIGGER [TableName]LastModifiedDate ON [TableName] FOR UPDATE AS UPDATE [TableName] SET [TableName].LastModified=getdate() FROM [TableName] INNER JOIN Inserted ON [TableName].[UniqueID]= Inserted.[UniqueID]

the problem is that in sqllite is says theres no table Inserted...
Currantly when I update one row all the rows datemodified are updated.. that's no good. I want just the one updated now should be changed.

Is there any other way to do it?

Thanks
0
Comment
Question by:Mr_Ezi
  • 3
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35065594
>>Zones: Databases Miscellaneous, MS SQL Server, SQL Query Syntax<<
This question has nothing to do with MS SQL Server, please request that zone be removed.
0
 
LVL 31

Expert Comment

by:Olaf Doschke
ID: 35066939
See the SQLite language refernce:
http://www.sqlite.org/lang_createtrigger.html

SQLite has no table inserted, it has "new" and "old"

Also getdate() is not a function available in SQLite, you have date('now') there.

T-SQL is T-SQL SQLite is SQLite. Learn SQLite, don't just copy and paste, this won't work of course.

Bye, Olaf.
0
 

Author Comment

by:Mr_Ezi
ID: 35068868
Thanks for the link I have a SqlLite book and I know the getdate function, but I can't find a solution for my problem.
I tried 'where new.oid = old.oid' but still all rows get affected!
Please help...
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
LVL 31

Expert Comment

by:Olaf Doschke
ID: 35129034
INNER JOIN Inserted ON [TableName].[UniqueID]= Inserted.[UniqueID] need to be replaced by
WHERE [TableName].[UniqueID]= new.[UniqueID] to limit the update on the single new record.

Bye, Olaf.
0
 
LVL 31

Accepted Solution

by:
Olaf Doschke earned 2000 total points
ID: 35129067
Also see: http://linuxgazette.net/109/chirico1.html

Sample code
     -- ********************************************************************
     --   Creating a trigger for timeEnter
     --     Run as follows:
     --            $ sqlite3 test.db < trigger1
     -- ********************************************************************
     CREATE TRIGGER insert_t1_timeEnter AFTER  INSERT ON t1
     BEGIN
      UPDATE t1 SET timeEnter = DATETIME('NOW')  WHERE rowid = new.rowid;
     END;
     -- ********************************************************************

Open in new window


Should be easy enough to adapt to your situation.

Bye, Olaf.
0
 

Author Closing Comment

by:Mr_Ezi
ID: 35129277
Thnaks your the best.
0

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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.

Join & Write a Comment

Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…

607 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