Link to home
Start Free TrialLog in
Avatar of AWestEng
AWestEngFlag for Sweden

asked on

INSERT, Stored procedure

Hi guys, need some help with mysql and stored procedures.

I'm using MySQL 5, I need to create an INSERT stored procedure for this table ,how do I do that?


CREATE TABLE `tbl_logging` (
  `LogID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Priority` decimal(10,0) NOT NULL,
  `Severity` varchar(200) NOT NULL DEFAULT 'Missing',
  `Title` varchar(200) NOT NULL DEFAULT 'Missing',
  `Timestamp` varchar(200) NOT NULL DEFAULT 'Missing',
  `HostName` varchar(200) NOT NULL DEFAULT 'Missing',
  `MachineName` varchar(200) NOT NULL DEFAULT 'Missing',
  `AppDomainName` varchar(200) NOT NULL DEFAULT 'Missing',
  `ProcessID` varchar(200) NOT NULL DEFAULT 'Missing',
  `ProcessName` varchar(200) NOT NULL DEFAULT 'Missing',
  `ThreadName` varchar(200) NOT NULL DEFAULT 'Missing',
  `Win32ThreadId` varchar(200) NOT NULL DEFAULT 'Missing',
  `Message` varchar(200) NOT NULL DEFAULT 'Missing',
  `Revision` varchar(200) NOT NULL DEFAULT 'Missing',
  PRIMARY KEY (`LogID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Avatar of TKorhan
TKorhan

For Trigger:
CREATE TRIGGER `tbl_logging_after_ins_tr` AFTER INSERT ON `tbl_logging`
  FOR EACH ROW
BEGIN

    CALL ExampeProcedure('a');

END;


For Procedure:
CREATE PROCEDURE `ExampeProcedure`( IN smthng VARCHAR(1))
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  IF (smthng i="A")THEN
    ...
  ELSEIF
  ...
  END IF;
END;
Avatar of AWestEng

ASKER

oki thx.

is it possible to pass all insert parameters to the Stored procedure

So i don't neeed to create the query each time in the application

I meen, can I call the Procedure like a function with inparameters?
Off Course, You can use like :

For Trigger:
CREATE TRIGGER `tbl_logging_after_ins_tr` AFTER INSERT ON `tbl_logging`
  FOR EACH ROW
BEGIN

    CALL ExampeProcedure();

END;


For Procedure:
CREATE PROCEDURE `ExampeProcedure`()
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  ...
  ...
END;
hmm I'm not sure I got that.

I meean in this case I don't need a trigger. the only thing I need to do is call a Insert ´statment via a stored Procedure,

What I want is that the Procedure make the insert.  

so like this

CREATE PROCEDURE `ExampeProcedure`(Value1,Value2,Value3)
INSERT ...... To ... C1 = Value 1, C2 = Vlaue 2, C3 = Value3

I like to pass ALL values that I have to the stored Procedure and that Procedure  should create the INSERT sql query with that data and then insert it.
ASKER CERTIFIED SOLUTION
Avatar of TKorhan
TKorhan

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial