RopeADope Posted June 16, 2010 Share Posted June 16, 2010 Hi all. My post is twofold today. 1st Q: Can anyone provide me with a ridiculously simple snippet of code for a trigger that cascades a single field from tableA to tableB? I've searched around but my brain just goes haywire with the examples that I've seen. If not a snippet, then a variable representation would work(e.g. CREATE TRIGGER trigger_name INSERT table.field............END) 2nd Q: My goal is to basically cascade keys from parent to child tables(MyISAM). I'm aware that InnoDB does this automagically but from what I've read, its fairly easy to write a trigger(even though they confuse me :'(). Are triggers the best way to enforce the keys or would it be easier to write some PHP to do multiple update statements in SQL via the UNION option?(Just ask if clarification is needed on this one. I'm not entirely sure I got my point across) Link to comment https://forums.phpfreaks.com/topic/204946-help-with-triggersbest-practices-for-cascadeupdatedelete/ Share on other sites More sharing options...
fenway Posted June 16, 2010 Share Posted June 16, 2010 Hard to give an example of a trigger without knowing what it's supposed to do. The only reason to keep the logic in the DB is if your app isn't the only way to access it. Link to comment https://forums.phpfreaks.com/topic/204946-help-with-triggersbest-practices-for-cascadeupdatedelete/#findComment-1072953 Share on other sites More sharing options...
RopeADope Posted June 16, 2010 Author Share Posted June 16, 2010 Hard to give an example of a trigger without knowing what it's supposed to do. The only reason to keep the logic in the DB is if your app isn't the only way to access it. --:: Sorry. I need a trigger that will cascade an id to a child table from its parent table, when a new entry is made in the parent table. So something like "insert into parent_table, then cascade the insert to child_table". --So if I can keep the logic in my app, I should do that instead of confining the logic to the DB? Link to comment https://forums.phpfreaks.com/topic/204946-help-with-triggersbest-practices-for-cascadeupdatedelete/#findComment-1072964 Share on other sites More sharing options...
fenway Posted June 17, 2010 Share Posted June 17, 2010 --So if I can keep the logic in my app, I should do that instead of confining the logic to the DB? According to me, yes. Link to comment https://forums.phpfreaks.com/topic/204946-help-with-triggersbest-practices-for-cascadeupdatedelete/#findComment-1073450 Share on other sites More sharing options...
RopeADope Posted June 17, 2010 Author Share Posted June 17, 2010 --So if I can keep the logic in my app, I should do that instead of confining the logic to the DB? According to me, yes. Gotcha. So one thing I should probably mention(if I haven't already) is I'm converting this from MSAccess to MySQL. I didn't design the initial database or the queries(written in SQL). So here's a sample of the kind of queries I'm dealing with... SELECT Availability.AvailabilityID, Availability.AvailDate, Availability.ClientID, Availability.KPIAvailMinID, Availability.KPIAvailObjID, Availability.BusParaID, Availability.AvailIncid, Availability.AvailUnavil, Availability.AvailDownCServ, Availability.AvailDownCSys, Availability.AvailDownAppDev, Availability.AvailDownInfra, Availability.UtilUnavail, Availability.AvailAuto, Availability.Availproc, Availability.TotalAvail, Availability.VendMissed, Availability.CSrvOLATargets, Availability.CSysOLATargets, Availability.AppDevOLATargets, Availability.InfraOLATargets, Availability.InfSecOLATargets, Availability.VendTargets, Availability.CSrvOLAMissed, Availability.CSysOLAMissed, Availability.AppDevOLAMissed, Availability.InfraOLAMissed, Availability.InfSecOLAMissed, Availability.SecIncidents, Availability.HwSwNetCIs, Availability.HSNCIOrphans, Availability.NoAvailPlan, Availability.NoAvailReview, Availability.AvailComments, BusPara.P1Services, BusPara.P2Services, BusPara.P3Services, (1-[AvailUnavil]/[TotalAvail]) AS ServiceRelX, ([P1Services]+[P2Services])*30*24 AS TotalAvailability, (1- ([CSrvOLAMissed]+[CSysOLAMissed]+[AppDevOLAMissed]+[infraOLAMissed]+[infSecO LAMissed])/([CSrvOLATargets]+[CSysOLATargets]+[AppDevOLATargets]+[infraOLATar gets]+[infSecOLATargets])) AS IntsupRel, (1-([VendMissed]/[VendTargets])) AS VendSupRel, ([HSNCIOrphans]/[HwSwNetCIs]) AS ServabilX, ([NoAvailPlan]/[busPara]![ActiveServices]) AS AvailRiskX, (1- ([NoAvailPlan]/[busPara]![ActiveServices])) AS CQIX, ([utilUnavail]/[TotalUtilAvail]) AS AvailUtil So with that in mind, is your advice to still confine the logic to my app? Link to comment https://forums.phpfreaks.com/topic/204946-help-with-triggersbest-practices-for-cascadeupdatedelete/#findComment-1073467 Share on other sites More sharing options...
fenway Posted June 17, 2010 Share Posted June 17, 2010 Yikes. Why not Availability.*? Link to comment https://forums.phpfreaks.com/topic/204946-help-with-triggersbest-practices-for-cascadeupdatedelete/#findComment-1073471 Share on other sites More sharing options...
RopeADope Posted June 17, 2010 Author Share Posted June 17, 2010 Yikes. Why not Availability.*? That's the nature of Microsoft? lol. This was created by my boss, whom had to read a book on Access before creating it, so I'm sure this is not the first issue I'll run into...So anyway, my previous question, keep it in the DB or make it app based?(the above query is just one of many) Link to comment https://forums.phpfreaks.com/topic/204946-help-with-triggersbest-practices-for-cascadeupdatedelete/#findComment-1073490 Share on other sites More sharing options...
fenway Posted June 17, 2010 Share Posted June 17, 2010 Moving it to the DB should be the last step, if at all. Link to comment https://forums.phpfreaks.com/topic/204946-help-with-triggersbest-practices-for-cascadeupdatedelete/#findComment-1073590 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.