...
RuleId: row identifier used in the update triggerSaOMIT : for further use
SaRESTRICT : for further use
CreateDate: datetime of record creationModifDate : datetime of record update (updated by a trigger in the sample)
SQL Server
We provide a sample for SQL Server that you can use and fill with your own data
...
Rules will not work correctly- when there are two external app with same names as we don't get the AppID from external sources it's too difficult to do for that so we work on distinct/unique names of the apps.
MySql / MariaDb
| Code Block |
|---|
-- MySQL DDL Script
-- Converted from SQL Server by ChatGPT
-- Ebiexperts 2024-03-08 JPG (MySQL Version)
-- Drop table if exists
DROP TABLE IF EXISTS SAM_ExtRules;
-- Create table
CREATE TABLE SAM_ExtRules (
RuleId INT AUTO_INCREMENT PRIMARY KEY,
AppName VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
ActorName VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
FullName VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
SaACCESS VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'USER',
SaOMIT VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
SaRESTRICT TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP,
ModifDate DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Create index (MySQL does not have NONCLUSTERED keyword; all indexes are secondary except PK)
CREATE INDEX SAM_ExtRules_AppActorName_IDX ON SAM_ExtRules (AppName ASC, ActorName ASC);
CREATE INDEX SAM_ExtRules_AppName_IDX ON SAM_ExtRules (AppName ASC);
CREATE INDEX SAM_ExtRules_ActorName_IDX ON SAM_ExtRules (ActorName ASC);
-- No need for trigger in MySQL if ON UPDATE is used for ModifDate
-- But if you want explicit trigger logic, here's the equivalent:
-- Optional: If not using ON UPDATE on column
-- DELIMITER $$
-- CREATE TRIGGER trgAfterUpdate
-- BEFORE UPDATE ON SAM_ExtRules
-- FOR EACH ROW
-- BEGIN
-- SET NEW.ModifDate = CURRENT_TIMESTAMP;
-- END$$
-- DELIMITER ; |
UI Changes for the External Apps
...