Solved

Trigger in SqlLite

Posted on 2011-03-07
6
412 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 29

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 29

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 29

Accepted Solution

by:
Olaf Doschke earned 500 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

751 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