×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Stored Procedures and triggers

Stored Procedures and triggers

Stored Procedures and triggers

(OP)
Stored Procedures and triggers

I am still newish and inexperienced in MySql databases. I am using them for my Classic ASP websites.
Here is what I am seeking help with.
I have the following query:

CODE -->

DELETE FROM STORY_TAGS;

INSERT INTO STORY_TAGS (TAGS_)
SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(TAGS_,',',mCtr),',',-1) AS
 valsAsRows FROM (SELECT (cb.ctr*cc.ctr)+ca.ctr AS mCtr FROM (SELECT 1 ctr 
UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) ca CROSS JOIN (SELECT 0 ctr 
UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) cb CROSS JOIN 
(SELECT 0 ctr UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4)
 cc GROUP BY mCtr) mc CROSS JOIN STORIES GROUP BY valsAsRows; 

I need this to execute every time there is a new insert on the stories table(each time a new story is uploaded).
The query works when run manually. I tried using the trigger builder wizard that is is PhpMyAdmin.
That caused errors that I couldn't see(could not scroll).

How can I create and execute this trigger??

Thanks
Jeff

Thanks,
Penguin
Please keep in mind that I am using classic ASP with MySQL database. Not sure if that information helps or not.

RE: Stored Procedures and triggers

Instead of Stored Procedures and triggers that fire 'every time there is a new insert on the stories table', why not create a View? Especially that you Delete and re-populate entire STORY_TAGS table every time sad

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Stored Procedures and triggers

(OP)
Let's say, for the sake of an argument, that I do not empty the DB each time. How can I get this to trigger each time a new story is submitted??

Thanks,
Penguin
Please keep in mind that I am using classic ASP with MySQL database.

"It's nice to be important... but it's more important to be nice."

RE: Stored Procedures and triggers

"I do not empty the DB [STORY_TAGS table] each time" - you may introduce a column in STORY_TAGS table with a time stamp (MyTime?) as a default value. This way you can always ask for the MAX(MyTime) when you want to get the most recent value(s) of TAGS_

Here is some info about MySQL AFTER INSERT Trigger

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Stored Procedures and triggers

(OP)
I appreciate your feedback. This SQL statement works when ran alone. However, I am getting multiple errors when trying to create a trigger using it.

CODE -->

The following query has failed: "CREATE
DEFINER=`jeff.ferren`@`gmail.com` TRIGGER
`UpdateALLTags` AFTER INSERT ON `stories` FOR
EACH ROW DELETE FROM STORY_TAGS;
INSERT INTO STORY_TAGS (TAGS_) SELECT
DISTINCT
SUBSTRING_INDEX(SUBSTRING_INDEX(TAGS_,',',mCtr),',',-1)
AS valsAsRows FROM (SELECT
(cb.ctr*cc.ctr)+ca.ctr AS mCtr FROM (SELECT 1 ctr
UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4) ca CROSS JOIN (SELECT 0 ctr UNION
SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4) cb CROSS JOIN (SELECT 0 ctr
UNION SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4) cc GROUP BY mCtr)
mc CROSS JOIN STORIES GROUP BY
valsAsRows; "
MySQL said: #1064 - You have an error in your SQL
syntax; check the manual that corresponds to your
MariaDB server version for the right syntax to use
near 'INSERT INTO STORY_TAGS (TAGS_)
SELECT DISTINCT
SUBSTRING_INDEX(SUBSTRING_IND...' at line 4 

Thanks,
Penguin
Please keep in mind that I am using classic ASP with MySQL database.

"It's nice to be important... but it's more important to be nice."

RE: Stored Procedures and triggers

Just a guess here....

CODE

DELIMITER $$

CREATE TRIGGER UpdateALLTags
    AFTER INSERT
    ON stories FOR EACH ROW
BEGIN
    DELETE FROM STORY_TAGS;

    INSERT INTO STORY_TAGS (TAGS_) 
    SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(TAGS_,',',mCtr),',',-1) AS valsAsRows 
    FROM (SELECT (cb.ctr*cc.ctr)+ca.ctr AS mCtr 
      FROM (SELECT 1 ctr
      UNION SELECT 2 
      UNION SELECT 3 
      UNION SELECT 4) ca CROSS JOIN
         (SELECT 0 ctr 
    UNION SELECT 1 
    UNION SELECT 2 
    UNION SELECT 3
    UNION SELECT 4) cb CROSS JOIN 
         (SELECT 0 ctr
    UNION SELECT 1 
    UNION SELECT 2 
    UNION SELECT 3 
    UNION SELECT 4) cc GROUP BY mCtr)
    mc CROSS JOIN STORIES 
    GROUP BY valsAsRows;
END$$    

DELIMITER ; 

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Stored Procedures and triggers

Yes, I think that is the major point.

Take a look at any example of stored procedures in the documentation https://mariadb.com/kb/en/create-procedure/

The always start with defining a DELIMITER marking the end of the stored proc and after that set it back to semicolon.

Besides that, no matter if you knew about the delimiter, when you want a procedure to do multiple statements, ou have to have them in a BEGIN...END block.

Chriss

RE: Stored Procedures and triggers

(OP)
Thanks a bunch, Andrzejek!1

Chris Miller, I am always learning something new. This is at the top of the list

Thanks,
Penguin
Please keep in mind that I am using classic ASP with MySQL database.

"It's nice to be important... but it's more important to be nice."

RE: Stored Procedures and triggers

Did it work ponder
I still think the view (some people call it a 'virtual table') would be the better way to go.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Stored Procedures and triggers

(OP)
Andrzejek, yes it did. Thanks!!

Thanks,
Penguin
Please keep in mind that I am using classic ASP with MySQL database.

"It's nice to be important... but it's more important to be nice."

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close