Could this string be breaking Trigger? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Could this string be breaking Trigger?

Author

Topic

Treble6
Starting Member

2 Posts

Posted - 2011-08-22 : 15:19:15

I have a field in my database that occassionally gets populated with the following string:

O:8:"mgm_post":8:{s:11:"purchasable";s:1:"N";s:13:"purchase_cost";s:1:"0";s:15:"purchase_expiry";s:0:"";s:17:"purchase_duration";s:1:"0";s:7:"product";a:0:{}s:23:"access_membership_types";a:0:{}s:12:"access_delay";a:0:{}s:15:"access_duration";s:1:"0";}

I need to switch this string to a similar but altogether different string, this one:

O:8:"mgm_post":8:{s:11:"purchasable";s:1:"N";s:13:"purchase_cost";s:1:"4";s:15:"purchase_expiry";s:0:"";s:17:"purchase_duration";s:1:"0";s:7:"product";a:0:{}s:23:"access_membership_types";a:1:{i:0;s:6:"member";}s:12:"access_delay";a:4:{s:5:"guest";s:1:"0";s:5:"trial";s:1:"0";s:4:"free";s:1:"0";s:6:"member";s:1:"0";}s:15:"access_duration";s:1:"0";}

I dont claim to be very good at SQL but this is what i have come up with:

CREATE TRIGGER setMetaValue BEFORE INSERT ON wp_postmeta
FOR EACH ROW
BEGIN
IF NEW.meta_value = 'O:8:"mgm_post":8:{s:11:"purchasable";s:1:"N";s:13:"purchase_cost";s:1:"0";s:15:"purchase_expiry";s:0:"";s:17:"purchase_duration";s:1:"0";s:7:"product";a:0:{}s:23:"access_membership_types";a:0:{}s:12:"access_delay";a:0:{}s:15:"access_duration";s:1:"0";}' THEN

SET NEW.meta_value = 'O:8:"mgm_post":8:{s:11:"purchasable";s:1:"N";s:13:"purchase_cost";s:1:"4";s:15:"purchase_expiry";s:0:"";s:17:"purchase_duration";s:1:"0";s:7:"product";a:0:{}s:23:"access_membership_types";a:1:{i:0;s:6:"member";}s:12:"access_delay";a:4:{s:5:"guest";s:1:"0";s:5:"trial";s:1:"0";s:4:"free";s:1:"0";s:6:"member";s:1:"0";}s:15:"access_duration";s:1:"0";}';

END IF;
END $$

DELIMITER ;

However i get error warnings of syntax errors, could this be because of the characters in the string?
If this is true (even if its not) Can anyone help with a fix?

Regards

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-22 : 16:03:50

Are you even using Microsoft SQL Server? Your syntax is so vastly different from T-SQL that I suspect you aren't using MSSQL which is what SQLTeam.com is about.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Treble6
Starting Member

2 Posts

Posted - 2011-08-22 : 16:25:02

My mistake, ive been on this for days and finding zero help so ive just copied it everywhere. Sorry!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources