Viewing file:  create_trigger_rg_regisDetails.php (7.85 KB) -rwxr-xr-x
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link rel="stylesheet" type="text/css" href="style.css" />
<?php
$dbreg = $_POST["dbreg"];
$dbmoreg = $_POST["dbmoreg"];
$dbppc = $_POST["dbppc"];
$localhost_s = $_POST["localhost_s"];
$localhost_u = $_POST["localhost_u"];
$localhost_p = $_POST["localhost_p"];
$dbNo = $_POST["dbNo"];
?><table class='tb_1' width='100%'>
<tr><th class='h2'><?php
if($dbNo==1){
echo 'CREATE TRIGGER rg_regisDetail';
}elseif($dbNo==2){
echo 'ตรวจสอบการเลื่อนชั้นปีที่เกิน 3 ครั้ง';
}
?></th></tr>
<tr><td>
<?php
/*
CREATE TABLE `T_studentSummary` (
`ssmStdId` int(11) NOT NULL DEFAULT '0',
`ssmTmId` int(1) NOT NULL DEFAULT '0',
`ssmAcY` int(4) NOT NULL DEFAULT '0',
`ssmGPA` float NOT NULL DEFAULT '0',
`ssmCreditAttempt` int(11) NOT NULL DEFAULT '0',
`ssmCreditSatisfy` int(11) NOT NULL DEFAULT '0',
`ssmCreditPoint` int(11) NOT NULL DEFAULT '0',
`ssmGPAX` float NOT NULL DEFAULT '0',
`ssmSumCreditAttempt` int(11) NOT NULL DEFAULT '0',
`ssmSumCreditSatisfy` int(11) NOT NULL DEFAULT '0',
`ssmSumCreditPoint` int(11) NOT NULL DEFAULT '0',
`ssmApprover` varchar(32) NOT NULL DEFAULT '',
`ssmRefNo` varchar(5) NOT NULL DEFAULT '',
`ssmApproveDate` date NOT NULL DEFAULT '0000-00-00',
`ssmPassStatus` int(11) NOT NULL DEFAULT '0',
`ssmSstId` int(11) NOT NULL DEFAULT '0',
`ssmSstIdApproveSpe` int(11) DEFAULT NULL,
`ssmSyId` int(4) NOT NULL DEFAULT '0',
`ssmCreateDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`ssmCreateUserId` varchar(20) NOT NULL DEFAULT '',
`ssmUpdateDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`ssmUpdateUserId` varchar(20) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
<br><br>
*/;
if($dbNo==1){
echo "
<br><br>
create trigger smd_trg1 AFTER INSERT ON rg_RegistDetails
for each row
begin
INSERT INTO T_registDetails values
(new.rdAcY,
new.rdStdId,
new.rdTmId,
new.rdCoId,
new.rdSeq,
new.rdCreditAttempt,
new.rdSection,
new.rdGrade,
new.rdDocNo,
new.rdCredit1,
new.rdCredit2,
new.rdNoCalGPA,
new.rdAllowPr,
new.rdCdId1,
new.rdCdId2,
new.rdUpdateDate,
new.rdUpdateUserId);
end$$
<br><br>
create trigger smd_trg2 AFTER UPDATE ON rg_RegistDetails
for each row
begin
INSERT INTO T_registDetails values
(old.rdAcY,
old.rdStdId,
old.rdTmId,
old.rdCoId,
old.rdSeq,
old.rdCreditAttempt,
old.rdSection,
old.rdGrade,
old.rdDocNo,
old.rdCredit1,
old.rdCredit2,
old.rdNoCalGPA,
old.rdAllowPr,
old.rdCdId1,
old.rdCdId2,
old.rdUpdateDate,
old.rdUpdateUserId);
end$$
<br><br>
create trigger smd_trg3 AFTER DELETE ON rg_RegistDetails
for each row
begin
INSERT INTO T_registDetails values
(old.rdAcY,
old.rdStdId,
old.rdTmId,
old.rdCoId,
old.rdSeq,
old.rdCreditAttempt,
old.rdSection,
old.rdGrade,
old.rdDocNo,
old.rdCredit1,
old.rdCredit2,
old.rdNoCalGPA,
old.rdAllowPr,
old.rdCdId1,
old.rdCdId2,
old.rdUpdateDate,
old.rdUpdateUserId);
end$$
<br><br>
create trigger smd_trg4 AFTER INSERT ON rg_StudentSummary
for each row
begin
INSERT INTO T_studentSummary values
(new.ssmStdId,
new.ssmTmId,
new.ssmAcY,
new.ssmGPA,
new.ssmCreditAttempt,
new.ssmCreditSatisfy,
new.ssmCreditPoint,
new.ssmGPAX,
new.ssmSumCreditAttempt,
new.ssmSumCreditSatisfy,
new.ssmSumCreditPoint,
new.ssmApprover,
new.ssmRefNo,
new.ssmApproveDate,
new.ssmPassStatus,
new.ssmSstId,
new.ssmSstIdApproveSpe,
new.ssmSyId,
new.ssmCreateDate,
new.ssmCreateUserId,
new.ssmUpdateDate,
new.ssmUpdateUserId);
end$$
<br><br>
create trigger smd_trg5 AFTER UPDATE ON rg_StudentSummary
for each row
begin
INSERT INTO T_studentSummary values
(old.ssmStdId,
old.ssmTmId,
old.ssmAcY,
old.ssmGPA,
old.ssmCreditAttempt,
old.ssmCreditSatisfy,
old.ssmCreditPoint,
old.ssmGPAX,
old.ssmSumCreditAttempt,
old.ssmSumCreditSatisfy,
old.ssmSumCreditPoint,
old.ssmApprover,
old.ssmRefNo,
old.ssmApproveDate,
old.ssmPassStatus,
old.ssmSstId,
old.ssmSstIdApproveSpe,
old.ssmSyId,
old.ssmCreateDate,
old.ssmCreateUserId,
old.ssmUpdateDate,
old.ssmUpdateUserId);
end$$
<br><br>
create trigger smd_trg6 AFTER DELETE ON rg_StudentSummary
for each row
begin
INSERT INTO T_studentSummary values
(old.ssmStdId,
old.ssmTmId,
old.ssmAcY,
old.ssmGPA,
old.ssmCreditAttempt,
old.ssmCreditSatisfy,
old.ssmCreditPoint,
old.ssmGPAX,
old.ssmSumCreditAttempt,
old.ssmSumCreditSatisfy,
old.ssmSumCreditPoint,
old.ssmApprover,
old.ssmRefNo,
old.ssmApproveDate,
old.ssmPassStatus,
old.ssmSstId,
old.ssmSstIdApproveSpe,
old.ssmSyId,
old.ssmCreateDate,
old.ssmCreateUserId,
old.ssmUpdateDate,
old.ssmUpdateUserId);
end$$
<br><br>
";
echo '<br><br>';
echo 'ใส่ค่า Delimiter = $$';
echo '<br><br>';
echo '-- คำสั่งดู TRIGGER ';
echo '<br>';
echo 'show triggers; ';
echo '<br><br>';
echo '-- คำสั่งลบ TRIGGER ';
echo '<br>';
echo 'drop trigger <ชื่อ trigger>; <br><br>';
}elseif($dbNo==2){
echo "SELECT studentId, count( passStatus ) passStatus
FROM $dbreg.StudentStatus
WHERE passStatus IN (1,2,3)
GROUP BY studentId
HAVING count( passStatus ) >3";
echo '<br /><br />';
echo "SELECT ssmStdId, count( ssmPassStatus ) passStatus
FROM $dbmoreg.rg_StudentSummary
WHERE ssmPassStatus IN (1,2,3)
GROUP BY ssmStdId
HAVING count( ssmPassStatus ) >3";
echo "<br><br>นำ ssmStdId ค้นหาในตาราง $dbmoreg.rg_StudentSummary ";
}elseif($dbNo==3){
echo "<br><br>-- ฐานข้อมูลใหม่แยกข้อมูลผู้ปกครองเป็น 3 ฟิลด์(คำนำหน้าชื่อ ชื่อและนามสกุล) ";
echo "<br><br>-- นำ run บน phpMyAdmin ";
echo "<br><br>-- คำสั่งแก้ไขข้อมูล <br>";
echo "UPDATE $dbreg.`StudentBio` SET parentName = REPLACE(REPLACE(REPLACE(parentName ,'นาย','นาย '),'นาง','นาง '),'นางสาว','นางสาว ')
,parentName = REPLACE(REPLACE(REPLACE(parentName ,SPACE(4),SPACE(1)) ,SPACE(3),SPACE(1)),SPACE(2),SPACE(1))
,parentName = TRIM(parentName)
,fatherName = REPLACE(REPLACE(REPLACE(fatherName ,SPACE(4),SPACE(1)) ,SPACE(3),SPACE(1)),SPACE(2),SPACE(1))
,fatherName = REPLACE(REPLACE(REPLACE(fatherName ,'นาย ','นาย'),'นาง ','นาง'),'นางสาว ','นางสาว')
,fatherName = TRIM(fatherName);
,motherName = REPLACE(REPLACE(REPLACE(motherName ,SPACE(4),SPACE(1)) ,SPACE(3),SPACE(1)),SPACE(2),SPACE(1))
,motherName = REPLACE(REPLACE(REPLACE(motherName ,'นาย ','นาย'),'นาง ','นาง'),'นางสาว ','นางสาว')
,motherName = TRIM(motherName);
";
echo "<br><br>-- คำสั่งทดสอบ <br>";
echo "SELECT sb.parentName
,(case when (LENGTH(sb.parentName)-LENGTH(REPLACE(sb.parentName, ' ', ''))) >1
then (select prefixId from Prefix where prefixName = TRIM(LEFT(sb.parentName,locate(' ',sb.parentName)-1)))
else NULL
end) as pf
,(case when (LENGTH(sb.parentName)-LENGTH(REPLACE(sb.parentName, ' ', ''))) >1
then TRIM(SUBSTRING(sb.parentName,locate(' ',sb.parentName),locate(' ',sb.parentName,locate(' ',sb.parentName)+1)-locate(' ',sb.parentName)))
else TRIM(LEFT(sb.parentName,locate(' ',sb.parentName)-1))
end) as fname
,(case when (LENGTH(sb.parentName)-LENGTH(REPLACE(sb.parentName, ' ', ''))) >1
then TRIM(SUBSTRING(sb.parentName,locate(' ',sb.parentName,locate(' ',sb.parentName)+1),CHAR_LENGTH(sb.parentName)))
else TRIM(SUBSTRING(sb.parentName,locate(' ',sb.parentName),CHAR_LENGTH(sb.parentName)))
end) AS lname
FROM $dbreg.StudentBio sb
";
}
?>
</td></tr>
</table>
|