Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Trigger in SqlLite

Posted on 2011-03-07
6
Medium Priority
?
417 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
[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 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 30

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 30

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 30

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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

719 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