marrowyung
asked on
MySQL trigger
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.
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.
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;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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think I can make change on this to get ride of it:
ActionBy_Audit=USER() ?
or there are no way anyway ?
ActionBy_Audit=USER() ?
or there are no way anyway ?
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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 ?
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 ?
>so how the USER() can be chagne to ? no way ?
no way, this is a system function which cannot be changed...
no way, this is a system function which cannot be changed...
ASKER
ok.
ASKER
please help to answer this:
https://www.experts-exchange.com/questions/28570501/MySQL-trigger-warning-and-error-handling-control.html
https://www.experts-exchange.com/questions/28570501/MySQL-trigger-warning-and-error-handling-control.html
ASKER
I am sorry that I cannot help on that one :(
ASKER
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 !
!
but target table was not found error has no problem ! amazing !
!
ASKER
please help on answering this:
https://www.experts-exchange.com/questions/28611481/NOWAIT-signal-for-a-MysQL-table.html
https://www.experts-exchange.com/questions/28611481/NOWAIT-signal-for-a-MysQL-table.html
unless the application user is the mysql login, mysql cannot know it, except if you store that information in a/the table...
please clarify