Database Trigger

73 Posts

mandledesign Reply #1, 12 years, 10 months ago

Not really the best place for this question but it is related to my MODX system.
I'm attempting to do a database trigger when a record is added to one of my custom tables made with a CMP I created.
Anyways it should send an email to an email address gathered from the mods_user_attributes table based on a predetermined number assigned in the comment section when a record is added and compared to the project number. I've never attempted to create a sql trigger before so this is my best shot. It won't let me insert the trigger because of syntax error. I'm sure there are other errors with it too. The documentation I was reading wasn't making a ton of sense to me. Any help would be appreciated

CREATE TRIGGER new_row_added
ON modx_notes
FOR INSERT
AS

SELECT @Project = project 
FROM INSERTED

SELECT @Email = email
FROM modx_user_attributes
WHERE comment = @Project

exec master.dbo.xp_sendmail 
@recipients = '@Email', 
@subject = 'Project Update', 
@message = 'Hello, a new note has been added to your project!  Login to get updated.'

5,888 Posts

Bruno17 Reply #2, 12 years, 10 months ago

doesn't pure php/xpdo work?

lets say you have a field user_id in your project and an aggregate in your xpdo-schema with:

<aggregate alias="User" class="modUser" local="user_id" foreign="id" cardinality="one" owner="foreign" />

and in your note an aggregate with:

<aggregate alias="Project" class="Project" local="project_id" foreign="id" cardinality="one" owner="foreign" />

and in your processor something like that:

$classname = 'Note';
$object = $modx->newObject($classname);
$object->fromArray($postArray);
if ($object->save(){
    if ($project = $object->getOne('Project')){
        if ($user = $project->getOne('User')){
            if ($profile = $user->getOne('Profile')){
                $email = $profile->get('email');
        
                //send the mail here

            }        
        }
    }
}

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

24,544 Posts

BobRay Reply #3, 12 years, 10 months ago

You could also use $modx->invokeEvent() to fire a system event in your CMPs code (either an existing event, or one you create). Then you could use a plugin attached to that event to send the email.

For that matter, why not just send the email in the code that writes to the table?

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

73 Posts

mandledesign Reply #4, 12 years, 10 months ago

Quote from: Bruno17 at Dec 19, 2013, 01:24 AM

doesn't pure php/xpdo work?

I should have explained that I used migxdb to create the CMP. I see there are places to add js on submit within the plugin. Any suggestions there? I thought maybe just doing a db trigger would be the best solution.

5,888 Posts

Bruno17 Reply #5, 12 years, 10 months ago

Quote from: mandledesign at Dec 19, 2013, 10:59 AM

Quote from: Bruno17 at Dec 19, 2013, 01:24 AM
doesn't pure php/xpdo work?

I should have explained that I used migxdb to create the CMP.

well then, a custom update.php - processor in your folder:

core/components/{yourPackage}/processors/mgr/default/update.php

or

core/components/{yourPackage}/processors/mgr/{yourProcessorsPath}/update.php

as a copy of

core/components/migx/processors/mgr/default/update.php

with something like the code above after

$object->save();

should do it.

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!