Subject | Re: PROBLEM WITH TRIGGERS AND VIEWS |
---|---|
Author | prokhorovav |
Post date | 2002-01-16T14:12:33Z |
I have created a simple database:
SET TERM ^ ;
CREATE PROCEDURE S_TEST (
F INTEGER,S VARCHAR(10))
RETURNS (R INTEGER)
AS
BEGIN
EXIT;
END^
SET TERM ; ^
CREATE TABLE TEST (F INTEGER,S VARCHAR(10));
CREATE VIEW VW_TEST(F,S)
AS
select
F,S
from TEST
join rdb$database on 1=1
;
SET TERM ^ ;
CREATE TRIGGER VW_TEST_AU0 FOR VW_TEST
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
POST_EVENT 'DUMMY_EVENT';
END
^
CREATE TRIGGER VW_TEST_BD FOR VW_TEST
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
POST_EVENT 'DUMMY_EVENT';
END
^
CREATE TRIGGER VW_TEST_BI FOR VW_TEST
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
POST_EVENT 'DUMMY_EVENT';
END
^
CREATE TRIGGER VW_TEST_BU FOR VW_TEST
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
POST_EVENT 'DUMMY_EVENT';
END
^
ALTER TRIGGER VW_TEST_AU0
as
DECLARE VARIABLE r integer;
begin
select r from s_test(2,'Ins S') into :r;
end
^
ALTER TRIGGER VW_TEST_BD
AS
BEGIN
DELETE FROM TEST
WHERE (F = OLD.F) AND
(S = OLD.S);
END
^
ALTER TRIGGER VW_TEST_BI
AS
BEGIN
INSERT INTO TEST (F,S)
VALUES (NEW.F,NEW.S);
END
^
ALTER TRIGGER VW_TEST_BU
AS
BEGIN
UPDATE TEST
SET F = NEW.F,
S = NEW.S
WHERE (F = OLD.F);
END
^
SET TERM ; ^
SET TERM ^ ;
ALTER PROCEDURE S_TEST (
F INTEGER,
S VARCHAR(10))
RETURNS (
R INTEGER)
AS
BEGIN
r=0;
insert into vw_test(F,S) values(-:F,'AU SP');
suspend;
END
^
SET TERM ; ^
And have executed:
insert into vw_test(F,S)
values (1,'Ins');
update vw_test
set f=2,
s='upd'
where f=1
Its work.
I'm seem you have problems with your procedure or trigger "after
update"
Best regards
Andrey Prokhorov
SET TERM ^ ;
CREATE PROCEDURE S_TEST (
F INTEGER,S VARCHAR(10))
RETURNS (R INTEGER)
AS
BEGIN
EXIT;
END^
SET TERM ; ^
CREATE TABLE TEST (F INTEGER,S VARCHAR(10));
CREATE VIEW VW_TEST(F,S)
AS
select
F,S
from TEST
join rdb$database on 1=1
;
SET TERM ^ ;
CREATE TRIGGER VW_TEST_AU0 FOR VW_TEST
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
POST_EVENT 'DUMMY_EVENT';
END
^
CREATE TRIGGER VW_TEST_BD FOR VW_TEST
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
POST_EVENT 'DUMMY_EVENT';
END
^
CREATE TRIGGER VW_TEST_BI FOR VW_TEST
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
POST_EVENT 'DUMMY_EVENT';
END
^
CREATE TRIGGER VW_TEST_BU FOR VW_TEST
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
POST_EVENT 'DUMMY_EVENT';
END
^
ALTER TRIGGER VW_TEST_AU0
as
DECLARE VARIABLE r integer;
begin
select r from s_test(2,'Ins S') into :r;
end
^
ALTER TRIGGER VW_TEST_BD
AS
BEGIN
DELETE FROM TEST
WHERE (F = OLD.F) AND
(S = OLD.S);
END
^
ALTER TRIGGER VW_TEST_BI
AS
BEGIN
INSERT INTO TEST (F,S)
VALUES (NEW.F,NEW.S);
END
^
ALTER TRIGGER VW_TEST_BU
AS
BEGIN
UPDATE TEST
SET F = NEW.F,
S = NEW.S
WHERE (F = OLD.F);
END
^
SET TERM ; ^
SET TERM ^ ;
ALTER PROCEDURE S_TEST (
F INTEGER,
S VARCHAR(10))
RETURNS (
R INTEGER)
AS
BEGIN
r=0;
insert into vw_test(F,S) values(-:F,'AU SP');
suspend;
END
^
SET TERM ; ^
And have executed:
insert into vw_test(F,S)
values (1,'Ins');
update vw_test
set f=2,
s='upd'
where f=1
Its work.
I'm seem you have problems with your procedure or trigger "after
update"
Best regards
Andrey Prokhorov