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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: | 5e74d0964b8fd99eda798e3737217aa4 |
| User & Date: | danielk1977 2002-05-28 06:55:27 |
Context
|
2002-05-29
| ||
| 12:44 | Remove extra \ characters from temporary filenames under windows. (Ticket #52) (CVS 596) check-in: ed11abc8 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: 5e74d096 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: f8041f3d user: drh tags: trunk | |
Changes
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
...
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 {
................................................................................
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
...
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 {
................................................................................
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 ... 451 452 453 454 455 456 457 458 459 460 461 462 463 464 ... 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 |
#
# 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>
................................................................................
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 {
................................................................................
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 {
|
| > > > > > > > > > > > > > > > < < < < < < |
1 2 3 4 5 6 7 8 9 10 11 ... 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 ... 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 |
#
# 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>
................................................................................
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 {
................................................................................
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 {
|