Ufdjrw

Find a path from s to t using as few red nodes as possible

Strange use of "whether ... than ..." in official text

Arrows in tikz Markov chain diagram overlap

What is a typical Mizrachi Seder like?

What did the word "leisure" mean in late 18th Century usage?

How can I prove that a state of equilibrium is unstable?

Is the 21st century's idea of "freedom of speech" based on precedent?

Creating a script with console commands

Incomplete cube

A hang glider, sudden unexpected lift to 25,000 feet altitude, what could do this?

Man transported from Alternate World into ours by a Neutrino Detector

Does the Idaho Potato Commission associate potato skins with healthy eating?

What is the difference between 서고 and 도서관?

How can I separate the number from the unit in argument?

Finitely generated matrix groups whose eigenvalues are all algebraic

Gauss' Posthumous Publications?

Small nick on power cord from an electric alarm clock, and copper wiring exposed but intact

How dangerous is XSS

Why did the Drakh emissary look so blurred in S04:E11 "Lines of Communication"?

How can a day be of 24 hours?

How to show a landlord what we have in savings?

Can I cast Thunderwave and be at the center of its bottom face, but not be affected by it?

My ex-girlfriend uses my Apple ID to login to her iPad, do I have to give her my Apple ID password to reset it?

What happens if you break a law in another country outside of that country?

MySQL Trigger Error when trying to generate end_date from start_date + duration

0

Error Message

table

Hi

I am receiving this message when I try to use this trigger to calculate a end_date. The duration Integer is from another table. This is being done in MySQL on Mamp enviorment

Any help appreciated

code used
DELIMITER //

CREATE TRIGGER m_duration_ins_tr1

BEFORE INSERT ON memberships
FOR EACH ROW
BEGIN
DECLARE duration INTEGER;
SELECT duration FROM membership_type WHERE
membership_type_id = NEW.member_type;
SET NEW.end_date := DATE_ADD(NEW.start_date, INTERVAL duration MONTH);
END //
DELIMITER ;

mysql triggers mamp

share|improve this question

edited Mar 7 at 19:58

C.Haugh

asked Mar 7 at 19:53

C.HaughC.Haugh

32

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't understand why this is a trigger at all!?!
  
  – Strawberry
  Mar 7 at 20:03
  

  
  
  
  

add a comment | 

0

Error Message

table

Hi

I am receiving this message when I try to use this trigger to calculate a end_date. The duration Integer is from another table. This is being done in MySQL on Mamp enviorment

Any help appreciated

code used
DELIMITER //

CREATE TRIGGER m_duration_ins_tr1

BEFORE INSERT ON memberships
FOR EACH ROW
BEGIN
DECLARE duration INTEGER;
SELECT duration FROM membership_type WHERE
membership_type_id = NEW.member_type;
SET NEW.end_date := DATE_ADD(NEW.start_date, INTERVAL duration MONTH);
END //
DELIMITER ;

mysql triggers mamp

share|improve this question

edited Mar 7 at 19:58

C.Haugh

asked Mar 7 at 19:53

C.HaughC.Haugh

32

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't understand why this is a trigger at all!?!
  
  – Strawberry
  Mar 7 at 20:03
  

  
  
  
  

add a comment | 

Error Message

table

Hi

I am receiving this message when I try to use this trigger to calculate a end_date. The duration Integer is from another table. This is being done in MySQL on Mamp enviorment

Any help appreciated

code used
DELIMITER //

CREATE TRIGGER m_duration_ins_tr1

BEFORE INSERT ON memberships
FOR EACH ROW
BEGIN
DECLARE duration INTEGER;
SELECT duration FROM membership_type WHERE
membership_type_id = NEW.member_type;
SET NEW.end_date := DATE_ADD(NEW.start_date, INTERVAL duration MONTH);
END //
DELIMITER ;

mysql triggers mamp

share|improve this question

edited Mar 7 at 19:58

C.Haugh

asked Mar 7 at 19:53

C.HaughC.Haugh

32

share|improve this question

edited Mar 7 at 19:58

C.Haugh

asked Mar 7 at 19:53

C.HaughC.Haugh

32

share|improve this question

edited Mar 7 at 19:58

C.Haugh

asked Mar 7 at 19:53

C.HaughC.Haugh

32

asked Mar 7 at 19:53

C.HaughC.Haugh

32

asked Mar 7 at 19:53

C.HaughC.Haugh

32

2 Answers
2

active

oldest

votes

0

I think you just need to replace

SELECT duration FROM membership_type WHERE
membership_type_id = NEW.member_type;

with

SET duration = SELECT duration FROM membership_type WHERE membership_type_id = NEW.member_type LIMIT 1;

The bare SELECT was doing nothing to assign to your local variable, but was indeed basically an attempt to return a result set from your trigger as the error suggested.

share|improve this answer

answered Mar 7 at 23:09

UueerdoUueerdo

12.3k1816

add a comment | 

0

trigger event can be INSERT , UPDATE or DELETE . 
I dont think you can select .
Try mysql workbench to create trigger

share|improve this answer

answered Mar 7 at 19:57

Jin ThakurJin Thakur

72767

add a comment | 

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55051826%2fmysql-trigger-error-when-trying-to-generate-end-date-from-start-date-duration%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

I think you just need to replace

SELECT duration FROM membership_type WHERE
membership_type_id = NEW.member_type;

with

SET duration = SELECT duration FROM membership_type WHERE membership_type_id = NEW.member_type LIMIT 1;

The bare SELECT was doing nothing to assign to your local variable, but was indeed basically an attempt to return a result set from your trigger as the error suggested.

share|improve this answer

answered Mar 7 at 23:09

UueerdoUueerdo

12.3k1816

add a comment | 

0

I think you just need to replace

SELECT duration FROM membership_type WHERE
membership_type_id = NEW.member_type;

with

SET duration = SELECT duration FROM membership_type WHERE membership_type_id = NEW.member_type LIMIT 1;

The bare SELECT was doing nothing to assign to your local variable, but was indeed basically an attempt to return a result set from your trigger as the error suggested.

share|improve this answer

answered Mar 7 at 23:09

UueerdoUueerdo

12.3k1816

add a comment | 

I think you just need to replace

SELECT duration FROM membership_type WHERE
membership_type_id = NEW.member_type;

with

SET duration = SELECT duration FROM membership_type WHERE membership_type_id = NEW.member_type LIMIT 1;

The bare SELECT was doing nothing to assign to your local variable, but was indeed basically an attempt to return a result set from your trigger as the error suggested.

share|improve this answer

answered Mar 7 at 23:09

UueerdoUueerdo

12.3k1816

share|improve this answer

answered Mar 7 at 23:09

UueerdoUueerdo

12.3k1816

answered Mar 7 at 23:09

UueerdoUueerdo

12.3k1816

answered Mar 7 at 23:09

UueerdoUueerdo

12.3k1816

0

trigger event can be INSERT , UPDATE or DELETE . 
I dont think you can select .
Try mysql workbench to create trigger

share|improve this answer

answered Mar 7 at 19:57

Jin ThakurJin Thakur

72767

add a comment | 

0

trigger event can be INSERT , UPDATE or DELETE . 
I dont think you can select .
Try mysql workbench to create trigger

share|improve this answer

answered Mar 7 at 19:57

Jin ThakurJin Thakur

72767

add a comment | 

trigger event can be INSERT , UPDATE or DELETE . 
I dont think you can select .
Try mysql workbench to create trigger

share|improve this answer

answered Mar 7 at 19:57

Jin ThakurJin Thakur

72767

share|improve this answer

answered Mar 7 at 19:57

Jin ThakurJin Thakur

72767

answered Mar 7 at 19:57

Jin ThakurJin Thakur

72767

answered Mar 7 at 19:57

Jin ThakurJin Thakur

72767