I am working on an assignment and am pretty new to creating triggers. I have to create a trigger that will log who changed information on a table and when (NOT what they have changed). I was hoping someone could have a look and stop me before I head too far down the rabbit hole. I know there are more than just a couple errors but am more interested in making sure it is possible to do what I have in mind.My idea is to get the users name and see if it is in a table if not create it and get the associated ID, also get the ID of table that was accessed along with the ID of the type of task that was performed. Take this data and insert it into a table.Here is the SQL I have so far.-- Primary Database Tables --CREATE TABLE Physician ( Physician_ID int not null identity(1,1) primary key, First_Name varchar(100), Last_Name varchar(100), Mobile_Number varchar(15), Pager_Number varchar(15) ); CREATE TABLE Alert_Schedule ( Alert_ID int not null identity(1,1) primary key, Alert_Times varchar(25), Alert_Status varchar(25), Alert_Count int, Response varchar(255), Physician_ID int Foreign Key References Physician(Physician_ID) ); -- Auditing Tables --CREATE TABLE App_Audit_Tables ( Table_ID int not null identity(1,1) primary key, Table_Name varchar(20));CREATE TABLE App_Audit_Action_Type ( Action_Type_ID int not null identity(1,1) primary key, Action_Type_Desc varchar(255));CREATE TABLE App_Audit_User ( User_ID int not null identity(1,1) primary key, User_Name varchar(25)); CREATE TABLE App_Audit_Action ( Audit_Action_ID int not null identity(1,1) primary key, Aud_Incident_Date smalldatetime, Table_ID int Foreign Key References App_Audit_Tables(Table_ID), User_ID int Foreign Key References App_Audit_User(User_ID), Action_Type_ID int Foreign Key References App_Audit_Action_Type(Action_Type_ID));-- Insert Records in Primary Database Tables --INSERT INTO Physician (First_Name, Last_Name, Mobile_Number, Pager_Number) VALUES('Doogie','Howser','404-555-1212','');INSERT INTO Physician (First_Name, Last_Name, Mobile_Number, Pager_Number) VALUES('John','Spartan','','770-555-1122');INSERT INTO Alert_Schedule (Alert_Times, Alert_Status, Alert_Count, Response, Physician_ID) VALUES('0800-2030','On-Call',0,'',1);INSERT INTO Alert_Schedule (Alert_Times, Alert_Status, Alert_Count, Response, Physician_ID) VALUES('0800-2030','Standby',0,'',2);-- Insert Records in Auditing Tables --INSERT INTO App_Audit_Tables (Table_Name) VALUES ('Physician'), ('Alert_Schedule');INSERT INTO App_Audit_Action_Type (Action_Type_Desc) VALUES ('Login'), ('Update Physician'), ('Update Alert_Schedule'), ('Alter Physician'), ('Alter Alert_Schedule');-- Create Triggers --CREATE TRIGGER Trigger_Aud_Phy_Upd ON Physician AFTER INSERT, UPDATE, DELETE AS declare @tableID as int, @typeID as int, @userID as varchar(25); begin IF(SELECT User_ID from App_Audit_User) where User_Name = SYSTEM_USER begin set @userID = User_ID end ELSE begin INSERT INTO App_Audit_User (User_ID) VALUES (SYSTEM_USER); FROM inserted as @userID; end SELECT @tableID = Table_ID from App_Audit_Table where Table_Name = 'Physician'; SELECT @typeID = Action_Type_ID from App_Audit_Action_Type where Action_Type_Desc = 'Update Physician'; INSERT INTO App_Audit_Action (Table_ID, User_ID, Action_Type_ID, Aud_Incident_Date) VALUES (@tableID, @userID, @typeID, GETDATE()); endGO
Insert Update Delete Trigger
Posted - 2014-12-02 : 15:28:10
