Solved

MySQL trigger

Posted on 2014-10-13
12
433 Views
Last Modified: 2015-02-05
Dear all,

right now try to log down which application user change the record of a table, therefore I setup the table trigger for it.

CREATE  TRIGGER `WebHynet`.`tblPCCostItem_BDTRIGGER` 
BEFORE DELETE ON
 WebHynet.tblPCCostItem 
FOR EACH ROW
BEGIN 
INSERT INTO WebHynet_DataAudit.`tblPCCostItem` SET 
TriggerAction="BEFORE",
ActionDone="DELETE",
Action_AuditDate=now(),
ActionBy_Audit=USER() , 

`ID`=OLD.`ID`, `ReqDetailID`=OLD.`ReqDetailID`, `BudgetItemID`=OLD.`BudgetItemID`, `SignedCo`=OLD.`SignedCo`, `PrevID`=OLD.`PrevID`, `VendorType`=OLD.`VendorType`, `VendorCode`=OLD.`VendorCode`, `SRNum`=OLD.`SRNum`, `ProjectNum`=OLD.`ProjectNum`, `CostType`=OLD.`CostType`, `CostCat`=OLD.`CostCat`, `CostSubcat`=OLD.`CostSubcat`, `ReachCost`=OLD.`ReachCost`, `POFlag`=OLD.`POFlag`, `PONum`=OLD.`PONum`, `POLineNum`=OLD.`POLineNum`, `POMatchQty`=OLD.`POMatchQty`, `LISPONum`=OLD.`LISPONum`, `PODate`=OLD.`PODate`, `VendorContact`=OLD.`VendorContact`, `ItemCode`=OLD.`ItemCode`, `Description`=OLD.`Description`, `UOM`=OLD.`UOM`, `Currency`=OLD.`Currency`, `UnitCost`=OLD.`UnitCost`, `Quantity`=OLD.`Quantity`, `USDAmount`=OLD.`USDAmount`, `USDMonthlyCost`=OLD.`USDMonthlyCost`, `EvergreenFlag`=OLD.`EvergreenFlag`, `BillingPeriod`=OLD.`BillingPeriod`, `TermNoticeDay`=OLD.`TermNoticeDay`, `ReadyDate`=OLD.`ReadyDate`, `StartDate`=OLD.`StartDate`, `EndDate`=OLD.`EndDate`, `PopID`=OLD.`PopID`, `AendAddr`=OLD.`AendAddr`, `BendAddr`=OLD.`BendAddr`, `GLCode`=OLD.`GLCode`, `CCC`=OLD.`CCC`, `WONum`=OLD.`WONum`, `FirstReceiveQty`=OLD.`FirstReceiveQty`, `TotalReceiveQty`=OLD.`TotalReceiveQty`, `FirstReceiveDate`=OLD.`FirstReceiveDate`, `NextReceiveDate`=OLD.`NextReceiveDate`, `VendorDocNum`=OLD.`VendorDocNum`, `VendorCircuitID`=OLD.`VendorCircuitID`, `ItemStatus`=OLD.`ItemStatus`, `OccupyFlag`=OLD.`OccupyFlag`, `RequestUser`=OLD.`RequestUser`, `CreateUser`=OLD.`CreateUser`, `CreateDate`=OLD.`CreateDate`, `LastUpdUser`=OLD.`LastUpdUser`, `LastUpdDate`=OLD.`LastUpdDate`, `Nomenclate`=OLD.`Nomenclate`, `PCCWGServiceID`=OLD.`PCCWGServiceID`, `TermDate`=OLD.`TermDate`, `Make`=OLD.`Make`, `ModelNum`=OLD.`ModelNum`, `PartNum`=OLD.`PartNum`, `SerialNum`=OLD.`SerialNum`, `PrevID_TermDate`=OLD.`PrevID_TermDate`, `TermReqID`=OLD.`TermReqID`, `Attachment`=OLD.`Attachment`, `BudgetType`=OLD.`BudgetType`, `PopID_Bend`=OLD.`PopID_Bend`, `Bandwidth`=OLD.`Bandwidth`, `Bandwidth_byMI`=OLD.`Bandwidth_byMI`, `HalfCircuit`=OLD.`HalfCircuit`, `Protected`=OLD.`Protected`, `MiuPointUnit`=OLD.`MiuPointUnit`, `MiuPointAmt`=OLD.`MiuPointAmt`, `CostNature`=OLD.`CostNature`; END;

Open in new window


however this line of code:

ActionBy_Audit=USER()

seems not recording the application user for me but system user's name, how can I fix it.

The reason is this colume will most likely give me the same name.
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40376711
>the application user
unless the application user is the mysql login, mysql cannot know it, except if you store that information in a/the table...

please clarify
0
 
LVL 35

Accepted Solution

by:
Kimputer earned 250 total points
ID: 40376713
Your whole system seems to be programmed to have one MySQL account connecting to the database, hence you will only capture one same user.
You would have to REPROGRAM your WHOLE database system, backend and frontend, before you can capture what you need.
While every user has to log in, the actual MySQL communication is still done with that one account that's been configured.
Simple explanation: Install Wordpress (you need to supply MySQL username and password, for example wordpress). It will communicate with user wordpress with the MySQL server. The user you made inside Wordpress is just a user in its own Wordpress user table, NOT a MySQL user (could be done, but big security risk)! No matter how many users inside Wordpress you make, the communication with MySQL will always be the user wordpress (you only authenticate with another username/password for access to the pages, not the actual database connection). This can only be changed by totally reprogramming the Wordpress code (when logging in, switch database connection with new MySQL information)
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40379079
I think I can make change on this to get ride of it:

ActionBy_Audit=USER() ?

or there are no way anyway ?
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 40379286
see above: if each users logins in not using application users, but mysql users, then you can use USER()
otherwise the application user name needs to be provided into the table by the insert/update into the table directly, so that the trigger can use that field...
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40379335
"if each users logins in not using application users, but mysql users, then you can use USER()"

we are using applicatino users, so how the USER() can be chagne to ? no way ? that one must be the MySQL user anyway ?


and application user can only be handled in application anyway ?

"otherwise the application user name needs to be provided into the table by the insert/update into the table directly, so that the trigger can use that field... "

So this mean no need to use USER() as that username already inserted by the application on the source table already ? so trigger just copy that cell to the audti table ?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40380666
>so how the USER() can be chagne to ? no way ?
no way, this is a system function which cannot be changed...
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40381386
ok.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40480052
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40499815
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40499851
I am sorry that I cannot help on that one :(
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40499857
ok, no one can know this, I am a bit surprise. the default one doesn't jump out from the windows when the target table was lock for read operatoin.

but target table was not found error has no problem ! amazing !
!
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40592895
0

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql select total by week ending 3 43
Group By Function Required in PDF Output 21 66
I am new to using JSON in SQL Server 2 58
phpmyadmin memory error 55 95
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question