Stored Procedures and triggers

[penguinspeaks]

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:

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.

 

[Andrzejek]

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 :-(

---- Andy

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

 

[penguinspeaks]

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.

 

[Andrzejek]

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

 

[penguinspeaks]

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.

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.

 

[Andrzejek]

Just a guess here....

DELIMITER $$

CREATE TRIGGER UpdateALLTags
    AFTER INSERT
    ON stories FOR EACH ROW[red]
BEGIN[/red]
    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;[red]
END$$[/red]    

DELIMITER ;

---- Andy

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

 

[Chris Miller]

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

 

[penguinspeaks]

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.

 

[Andrzejek]

Did it work
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

 

[penguinspeaks]

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.