SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trigger not inserting data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amostoh
Starting Member

1 Posts

Posted - 03/10/2014 :  05:56:33  Show Profile  Reply with Quote
Hi all

will appreciate some help to debug the following trigger which is not inserting any data into `DUMPINGGRD`:

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 ;



The following is the structure of the tables involved:

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;

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 03/10/2014 :  15:42:30  Show Profile  Reply with Quote
Hi,
Which database are you using? SQL Server does not have a clause with BEFORE INSERT. You should use INSTEAD OF INSERT syntax for before insert trigger in SQL Server.

=======================
Not an Expert, Just a learner.
!_(M)_!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/11/2014 :  05:30:42  Show Profile  Reply with Quote
I can see InnoDB so its MYSQL I reckon. Please post in some MySQL forums in that case. This forum is MS SQL Server specific

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000