quote:

---

delimiter $$ CREATE TRIGGER ins_File BEFORE INSERT ON REPORT FOR EACH ROW BEGIN
set @filenum=(Select max(File_Id) from DUMPINGGRD);

if(@filenum=0) then --checking whether record is present or not
begin
new.File_Id=1;--if no record is present then set file_id=1
end;
else
new.File_Id=@filenum+1;--if record is present then set file_id=Maxvalue+1;
end if;
INSERT INTO DUMPINGGRD (File_Id) VALUES (NEW.File_Id);
END$$ delimiter ;

---

quote:

---

CREATE TABLE REPORT ( R_Id int(10) NOT NULL AUTO_INCREMENT,
R_Type varchar(255) not null,
R_Title varchar(255) not null,
U_Id int(10) not null,
File_Id int(10) not null,
PRIMARY KEY (R_Id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

---

quote:

---

CREATE TABLE `DUMPINGGRD`
(
`File_Id` int(10) NOT NULL,
`File_Name` varchar(255),
`UploadDate` date,
PRIMARY KEY (`File_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

---

quote:

---

ALTER TABLE `REPORT` ADD CONSTRAINT `fk_report` FOREIGN KEY (`File_Id`)
REFERENCES `DUMPINGGRD` (`File_Id`) ON UPDATE CASCADE;

---