我逻辑上的错误是什么?
在插入时,没有“old.? 只读new? 不用老一栏。 限定。
- old. is only available for DELETEs and UPDATEs
- new. is only available for INSERTs and UPDATEs
另外,您可能希望获得一个WHERE条款,而后一项WHEN条款则在触发触发启动时加以限制(这也要求获得关于普遍定期审议的任何途径的WHERE条款)。
UPDATE WaterwayOptions SET Length = (SELECT ST_Length(new.geom) FROM WaterwayOptions ) WHERE geom = new.geom;
如果你尝试使用WHEN条款,试图看看看新的<代码>geom是否以前不存在,那么你会有一个问题,因为“AFTER”在交易方面确实存在(EBEFORE受到警告)。
如果使用较为复杂的WHEN条款(见demo),发现新地段已经插入(见demo),那么UPDATE仍将更新所有各行。
因此,要求获得关于人民民主党的WHERE条款,因为关于人民民主党的WHERE条款很可能是最佳解决办法。
也许会考虑一下你似乎做些什么。
内容提要
- the original
- a second using the suggested WHERE clause,
- the third using a too simple WHEN clause, and finally
- the fourth using a more complex WHEN clause
除了建立TRIIGERTs之外,该法典是相同的(INSERTs the same data)。
The demo Code/SQL:-
/* ATTEMPT 1 (original)*/
DROP TABLE IF EXISTS WaterwayOptions;
CREATE TABLE IF NOT EXISTS WaterwayOptions (woId TEXT PRIMARY KEY, geom TEXT UNIQUE, `length` INTEGER, othercol TEXT DEFAULT blah );
CREATE TRIGGER update_Length_after_Insert AFTER INSERT
ON WaterwayOptions
BEGIN
UPDATE WaterwayOptions SET Length = (SELECT Length(new.geom) FROM WaterwayOptions );
END
;
INSERT INTO WaterwayOptions (woid,geom,`length`)
VALUES
( wo1 , geoma ,10),
( wo2 , geomaa ,10),
( wo3 , geomaaa ,10)
;
SELECT * FROM waterwayoptions;
/* ATTEMPT 2 (WHERE clause for the UPDATE)*/
DROP TABLE IF EXISTS WaterwayOptions;
CREATE TABLE IF NOT EXISTS WaterwayOptions (woId TEXT PRIMARY KEY, geom TEXT UNIQUE, `length` INTEGER, othercol TEXT DEFAULT blah );
DROP TRIGGER IF EXISTS update_Length_after_Insert;
CREATE TRIGGER update_Length_after_Insert AFTER INSERT
ON WaterwayOptions
BEGIN
UPDATE WaterwayOptions SET Length = (SELECT Length(new.geom) FROM WaterwayOptions ) WHERE geom=new.geom;
END
;
INSERT INTO WaterwayOptions (woid,geom,`length`)
VALUES
( wo1 , geoma ,10),
( wo2 , geomaa ,10),
( wo3 , geomaaa ,10)
;
SELECT * FROM waterwayoptions;
/* ATTEMPT 3 (simple WHEN) */
DROP TABLE IF EXISTS WaterwayOptions;
CREATE TABLE IF NOT EXISTS WaterwayOptions (woId TEXT PRIMARY KEY, geom TEXT UNIQUE, `length` INTEGER, othercol TEXT DEFAULT blah );
DROP TRIGGER IF EXISTS update_Length_after_Insert;
CREATE TRIGGER update_Length_after_Insert AFTER INSERT
ON WaterwayOptions WHEN new.geom NOT IN (SELECT geom FROM WaterwayOptions)
BEGIN
UPDATE WaterwayOptions SET Length = (SELECT Length(new.geom) FROM WaterwayOptions );
END
;
INSERT INTO WaterwayOptions (woid,geom,`length`)
VALUES
( wo1 , geoma ,10),
( wo2 , geomaa ,10),
( wo3 , geomaaa ,10)
;
SELECT * FROM waterwayoptions;
/* ATTEMPT 4 (more complex WHEN) */
DROP TABLE IF EXISTS WaterwayOptions;
CREATE TABLE IF NOT EXISTS WaterwayOptions (woId TEXT PRIMARY KEY, geom TEXT UNIQUE, `length` INTEGER, othercol TEXT DEFAULT blah );
DROP TRIGGER IF EXISTS update_Length_after_Insert;
CREATE TRIGGER update_Length_after_Insert AFTER INSERT
ON WaterwayOptions WHEN new.geom NOT IN (SELECT geom FROM WaterwayOptions WHERE geom <> new.geom)
BEGIN
UPDATE WaterwayOptions SET Length = (SELECT Length(new.geom) FROM WaterwayOptions);
END
;
INSERT INTO WaterwayOptions (woid,geom,`length`)
VALUES
( wo1 , geoma ,10),
( wo2 , geomaa ,10),
( wo3 , geomaaa ,10)
;
SELECT * FROM waterwayoptions;
成果(国家公务员系统之后的高等专业学校,因此,跨国激进党):
- First attempt ALL rows updated (incorrect)
UPDATE .... WHERE .... Each row has been updated to change 10 to the length of the geom column (correct)
- nothing UPDATED??? (new geom row exists so IS in the table!!!!)
