Submitted by: Carpenter, Tarl (goldshadow)

CREATE TABLE TEST1(
ID varchar(20) NOT NULL,
SVLOCID int NOT NULL,
SECONDID varchar(20) NOT NULL,
TRANSINFO Numeric(18,2) NOT NULL,
USERINFO varchar(20) NOT NULL,
USERDATE timestamp NOT NULL,
CONSTRAINT PK_TEST1 PRIMARY KEY (ID)
);

CREATE TABLE TEST2 (
ID varchar(20) NOT NULL,
SVLOCID int NOT NULL,
SECONDID varchar(20) NOT NULL,
TRANSINFO Numeric(18,2) NOT NULL,
USERINFO varchar(20) NOT NULL,
USERDATE timestamp NOT NULL,
CONSTRAINT PK_TEST2 PRIMARY KEY (ID)
);
COMMIT;

SET TERM ^ ;
CREATE TRIGGER TEST1_BI FOR TEST1
BEFORE INSERT POSITION 0
AS
BEGIN
insert into test2
values(http://new.id,new.svlocid,new.secondid,new.transinfo,new.userinfo,new.userdate);
END^
SET TERM ; ^

insert into test1 values('a',1,'b',20.4,'user1',cast('now' as date));
COMMIT;
/* two directions to go from here */

/*(First Direction) lets me alter the table with out warning of a read/write column mismatch*/
ALTER TABLE TEST2 ADD
testfield Varchar(20) NOT NULL;
COMMIT;
UPDATE TEST2
SET testfield = 'test'
WHERE testfield IS NULL;
COMMIT;

/*fails here */
insert into test1 values('b',1,'b',24.3,'user1',cast('now' as date));
COMMIT;

/*(Second Direction) if instead I use a nullable testfield column then insert will succeed with a null for the testfield column */
ALTER TABLE TEST2 ADD
testfield Varchar(20) NOT NULL;
COMMIT;
UPDATE TEST2
SET testfield = 'test'
WHERE testfield IS NULL;
COMMIT;

/*succeeds here without warning of column count mismatch*/
insert into test1 values('b',1,'b',24.3,'user1',cast('now' as date));
COMMIT;