Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

Trigger in SqlLite

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
Mr_Ezi
Asked:
Mr_Ezi
  • 3
  • 2
1 Solution
 
Anthony PerkinsCommented:
>>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
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
Mr_EziAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Olaf DoschkeSoftware DeveloperCommented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
Mr_EziAuthor Commented:
Thnaks your the best.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now