SQLite

Check-in [5e74d0964b]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update trigger2.test to match checkin 591. Also fix ticket #51 (by documenting problem) (CVS 595)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 5e74d0964b8fd99eda798e3737217aa499cc1726
User & Date: danielk1977 2002-05-28 06:55:27.000
Context
2002-05-29
12:44
Remove extra \ characters from temporary filenames under windows. (Ticket #52) (CVS 596) (check-in: ed11abc81e user: drh tags: trunk)
2002-05-28
06:55
Update trigger2.test to match checkin 591. Also fix ticket #51 (by documenting problem) (CVS 595) (check-in: 5e74d0964b user: danielk1977 tags: trunk)
2002-05-27
12:24
A SELECT statement inside the body of a TRIGGER uses the SRT_Discard target to discard the query results. Such selects are intended to be used to call user-defined functions for their side-effects. They do not return results. (CVS 594) (check-in: f8041f3d4d user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/trigger2.test.
︙︙
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595

596

597
598

  INSERT INTO cd VALUES (3, 4);

  CREATE TABLE tlog(ii INTEGER PRIMARY KEY, 
      olda, oldb, oldc, oldd, newa, newb, newc, newd);

  CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd;

  CREATE TRIGGER before_update BEFORE UPDATE ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
  END;
  CREATE TRIGGER after_update AFTER UPDATE ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
  END;

  CREATE TRIGGER before_delete BEFORE DELETE ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	old.a, old.b, old.c, old.d, 0, 0, 0, 0);
  END;
  CREATE TRIGGER after_delete AFTER DELETE ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	old.a, old.b, old.c, old.d, 0, 0, 0, 0);
  END;

  CREATE TRIGGER before_insert BEFORE INSERT ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	0, 0, 0, 0, new.a, new.b, new.c, new.d);
  END;
   CREATE TRIGGER after_insert AFTER INSERT ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	0, 0, 0, 0, new.a, new.b, new.c, new.d);
   END;
  }
} {}

do_test trig-7.2 {
  execsql {
    UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
    DELETE FROM abcd WHERE a = 1;
    INSERT INTO abcd VALUES(10, 20, 30, 40);
    SELECT * FROM tlog;
  }
} [ list 1 1 2 3 4 100 25 3 4 \
         2 1 2 3 4 100 25 3 4 \
 3 1 2 3 4 0 0 0 0 4 1 2 3 4 0 0 0 0 \

 5 0 0 0 0 10 20 30 40 6 0 0 0 0 10 20 30 40 ]


finish_test








|



|




|



|




|



|















|
>
|
>



550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600

  INSERT INTO cd VALUES (3, 4);

  CREATE TABLE tlog(ii INTEGER PRIMARY KEY, 
      olda, oldb, oldc, oldd, newa, newb, newc, newd);

  CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd;

  CREATE TRIGGER before_update INSTEAD OF UPDATE ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
  END;
  CREATE TRIGGER after_update INSTEAD OF UPDATE ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
  END;

  CREATE TRIGGER before_delete INSTEAD OF DELETE ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	old.a, old.b, old.c, old.d, 0, 0, 0, 0);
  END;
  CREATE TRIGGER after_delete INSTEAD OF DELETE ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	old.a, old.b, old.c, old.d, 0, 0, 0, 0);
  END;

  CREATE TRIGGER before_insert INSTEAD OF INSERT ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	0, 0, 0, 0, new.a, new.b, new.c, new.d);
  END;
   CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	0, 0, 0, 0, new.a, new.b, new.c, new.d);
   END;
  }
} {}

do_test trig-7.2 {
  execsql {
    UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
    DELETE FROM abcd WHERE a = 1;
    INSERT INTO abcd VALUES(10, 20, 30, 40);
    SELECT * FROM tlog;
  }
} [ list 1 1 2 3 4 100 25 3 4 \
         2 1 2 3 4 100 25 3 4 \
	 3 1 2 3 4 0 0 0 0 \
	 4 1 2 3 4 0 0 0 0 \
	 5 0 0 0 0 10 20 30 40 \
	 6 0 0 0 0 10 20 30 40 ]

finish_test
Changes to www/lang.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.36 2002/05/26 23:24:41 danielk1977 Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.37 2002/05/28 06:55:27 danielk1977 Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
︙︙
451
452
453
454
455
456
457









458
459
460
461
462
463
464

  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;
}
puts {
<p>With this trigger installed, executing the statement:</p>
}









Example {
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
}
puts {
<p>causes the following to be automatically executed:</p>
}
Example {








>
>
>
>
>
>
>
>
>








451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473

  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;
}
puts {
<p>With this trigger installed, executing the statement:</p>
}
puts {
<p>Note that currently, triggers may behave oddly when created on tables
  with INTEGER PRIMARY KEY fields. If a BEFORE trigger program modifies the 
  INTEGER PRIMARY KEY field of a row that will be subsequently updated by the
  statement that causes the trigger to fire, then the update may not occur. 
  The workaround is to declare the table with a PRIMARY KEY column instead
  of an INTEGER PRIMARY KEY column.</p>
}

Example {
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
}
puts {
<p>causes the following to be automatically executed:</p>
}
Example {
︙︙
513
514
515
516
517
518
519






520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541


Section {DROP TABLE} droptable

Syntax {sql-command} {
DROP TABLE <table-name>
}







Section {DROP TRIGGER} droptrigger
Syntax {sql-statement} {
DROP TRIGGER <trigger-name>
}
puts { 
  <p>Used to drop a trigger from the database schema. Note that triggers
  are automatically dropped when the associated table is dropped.</p>
}

puts {
<p>The DROP TABLE statement consists of the keywords "DROP TABLE" followed
by the name of the table.  The table named is completely removed from
the disk.  The table can not be recovered.  All indices associated with
the table are also deleted.</p>}

Section {DROP VIEW} dropview

Syntax {sql-command} {
DROP VIEW <view-name>
}

puts {








>
>
>
>
>
>









<
<
<
<
<
<








522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543






544
545
546
547
548
549
550


Section {DROP TABLE} droptable

Syntax {sql-command} {
DROP TABLE <table-name>
}

puts {
<p>The DROP TABLE statement consists of the keywords "DROP TABLE" followed
by the name of the table.  The table named is completely removed from
the disk.  The table can not be recovered.  All indices associated with
the table are also deleted.</p>}

Section {DROP TRIGGER} droptrigger
Syntax {sql-statement} {
DROP TRIGGER <trigger-name>
}
puts { 
  <p>Used to drop a trigger from the database schema. Note that triggers
  are automatically dropped when the associated table is dropped.</p>
}







Section {DROP VIEW} dropview

Syntax {sql-command} {
DROP VIEW <view-name>
}

puts {
︙︙