SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE TRANSACTIONS ( [Account] VARCHAR(32) NOT NULL, [TransID] UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(), [TransDate] DATETIME NOT NULL, [Location] VARCHAR(128) NOT NULL, [Description] VARCHAR(128) NOT NULL, [Direction] VARCHAR(1) NOT NULL DEFAULT 'O', [Amount] DECIMAL(10,2) NOT NULL, CONSTRAINT [PK_TRANSACTIONS] PRIMARY KEY ([Account] ASC, [TransID] ASC) ); CREATE TABLE HISTORY ( [HistID] INT NOT NULL IDENTITY(1,1), [HistDate] DATETIME NOT NULL DEFAULT GETDATE(), [Account] VARCHAR(32) NOT NULL, [TransID] UNIQUEIDENTIFIER NOT NULL, [TransDate] DATETIME NOT NULL, [Direction] VARCHAR(1) NOT NULL, [Amount] DECIMAL(10,2) NOT NULL, CONSTRAINT [PK_AUDIT] PRIMARY KEY ([HistID] ASC), CONSTRAINT [FK_AUDIT_TRANSACTIONS] FOREIGN KEY ([Account], [TransID]) REFERENCES TRANSACTIONS([Account], [TransID]) ); IF OBJECT_ID('AIT_TRANSACTIONS_HISTORY', 'TR') IS NOT NULL BEGIN DROP TRIGGER AIT_TRANSACTIONS_HISTORY; END; GO CREATE TRIGGER AIT_TRANSACTIONS_HISTORY ON TRANSACTIONS AFTER INSERT AS BEGIN INSERT INTO HISTORY (HistDate, Account, TransID, TransDate, Direction, Amount) SELECT GETDATE(), Account, TransID, TransDate, Direction, Amount FROM inserted; END; GO INSERT INTO TRANSACTIONS (Account, TransDate, Location, Description, Amount) VALUES ( '55396f7b60bb', CAST('2024-10-17 16:22:00' AS DATETIME), 'BW3s', 'Purchase', -57.63 ); INSERT INTO TRANSACTIONS (Account, TransDate, Location, Description, Amount) VALUES ( '55396f7b60bb', CAST('2024-9-20 15:48:00' AS DATETIME), 'Shell', 'Purchase', -40.00 ); INSERT INTO TRANSACTIONS (Account, TransDate, Location, Description, Amount) VALUES ( '55396f7b60bb', CAST('2024-9-19 18:30:00' AS DATETIME), 'Online', 'Payroll', 1845.23 ); INSERT INTO TRANSACTIONS (Account, TransDate, Location, Description, Amount) VALUES ( '55396f7b60bb', CAST('2024-9-18 12:04:00' AS DATETIME), 'Online', 'Overdraw Fee', -35.00 ); INSERT INTO TRANSACTIONS (Account, TransDate, Location, Description, Amount) VALUES ( '55396f7b60bb', CAST('2024-9-17 12:04:00' AS DATETIME), 'Chilis', 'Purchase', -44.28 ); INSERT INTO TRANSACTIONS (Account, TransDate, Location, Description, Amount) VALUES ( '55396f7b60bb', CAST('2024-9-17 8:45:00' AS DATETIME), 'Amazon', 'Purchase', -243.34 ); INSERT INTO TRANSACTIONS (Account, TransDate, Location, Description, Amount) VALUES ( '55396f7b60bb', CAST('2024-9-16 19:23:00' AS DATETIME), 'Foot Locker', 'Purchase', -128.46 ); INSERT INTO TRANSACTIONS (Account, TransDate, Location, Description, Amount) VALUES ( '55396f7b60bb', CAST('2024-9-14 8:30:00' AS DATETIME), 'ATM', 'Withdrawal', -200.00 ); INSERT INTO TRANSACTIONS (Account, TransDate, Location, Description, Amount) VALUES ( '55396f7b60bb', CAST('2024-9-13 16:38:00' AS DATETIME), 'Main Branch', 'Withdrawal', -500.00 ); INSERT INTO TRANSACTIONS (Account, TransDate, Location, Description, Amount) VALUES ( '55396f7b60bb', CAST('2024-9-13 16:22:00' AS DATETIME), 'Main Branch', 'Deposit', 1150.00 ); -- Check Insertion SELECT TransDate, Location, Description, Amount FROM TRANSACTIONS WHERE Account = '55396f7b60bb'; SELECT HistID, HistDate, Account, TransID, TransDate, Amount FROM HISTORY;