CODEWITHSUNDEEP
sqlite
user11862294
user11862294

Reputation:

Table column has to be updated once its exceed in sqlite

I am using 'sqlite' as a backend for the application. I have a table called 'student' and it has columns like student-name,student-id,student-year-of-join(with date and time),student-passed-out(with date and time),student-status. I insert student-name,student-id,student-year-of-join,student-passed-out in the starting itself. Once the student-passed-out from the college automatically the student-status has to be update to 'passed out'.

I tried using the sqlite trigger,it's not working properly

create trigger student-passed-out after update on student when student-passed-out<=datetime(now)begin update set candidate_status='Passed'

Here the student joins in different date and time. What I want is once the student passed out time is expired or exceeded the status should change.

Upvotes: 0

Views: 44

Answers (1)

DinoCoderSaurus
DinoCoderSaurus

Reputation: 6520

As per sqlite doc:

Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted or updated using references of the form "NEW.column-name" and "OLD.column-name", where column-name is the name of a column from the table that the trigger is associated with. OLD and NEW references may only be used in triggers on events for
which they are relevant, as follows:

INSERT NEW references are valid

UPDATE NEW and OLD references are valid

DELETE OLD references are valid

The column name in the WHEN clause requires a NEW. or OLD. prefix.

Speaking of column names, using - in the column name is going to create a lot of work for you. The names will always have to be quoted, otherwise the -can be interpreted as a minus sign.

The update clause in the trigger is missing a table name (and likely a WHERE clause, as written it will update every row in the table).

--ADDENDA--

  • a before update trigger executes, obviously, before the record is updated in the database. But what happens next? The "actual" update happens. In this case the candidate_status column will change back to what it was before the trigger ran. Perhaps try after update trigger.
  • the syntax for the current date/time is datetime('now')
  • This update set candidate_status='PASSED'; is missing a table name and a WHERE clause. Without a WHERE clause, it will update every row in the table. Perhaps something like WHERE student_id = OLD.student_id is what you need.

Upvotes: 1

Related Questions