--PRGDatabaseAudit Table Creation:

CREATE TABLE dbo.PRGDatabaseAudit(
    database_name VARCHAR(75),
    table_name VARCHAR(75),
    event_type VARCHAR(75),
    login_name VARCHAR(75),
    sql_command NVARCHAR(2000),
    audit_time_date VARCHAR(75) NOT NULL);


--CREATE/ALTER/DROP Trigger Creation:

create TRIGGER trg_audit
    ON DATABASE
    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @EventData XML = EVENTDATA() 
INSERT INTO dbo.PRGDatabaseAudit(
    database_name,
    table_name,
    event_type,
    login_name,
    sql_command,
    audit_time_date)

VALUES
       (
	@eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
	@eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
	@eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)'),
	@eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'),
	@eventdata.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(2000)'),
	convert(varchar, getdate(), 20))

END;





--PRGSaleAudit Table Creation:

CREATE TABLE dbo.PRGSaleAudit(
	audit_id VARCHAR(75),
	property_id INT NOT NULL,
	login_name VARCHAR(75),
	transaction_date VARCHAR(75),
	event_type VARCHAR(75)
);



--INSERT Trigger Creation:

CREATE TRIGGER dbo.sales_audit
ON dbo.ASSIGNMENT
AFTER INSERT
AS
 DECLARE @login_name VARCHAR(128)
 
    SELECT  @login_name = login_name
    FROM    sys.dm_exec_sessions
    WHERE   session_id = @@SPID
BEGIN

    INSERT INTO dbo.PRGSaleAudit
	(audit_id, property_id, login_name, transaction_date, event_type)
    SELECT newid(), property_id, @login_name , getdate(),'INS'
	FROM inserted;
END;
GO


--DELETE Trigger Creation:

CREATE TRIGGER dbo.sales_audit_delete
ON dbo.ASSIGNMENT
AFTER DELETE
AS
 DECLARE @login_name VARCHAR(128)
 
    SELECT  @login_name = login_name
    FROM    sys.dm_exec_sessions
    WHERE   session_id = @@SPID
BEGIN

    INSERT INTO dbo.PRGSaleAudit
	(audit_id, property_id, login_name, transaction_date, event_type)
    SELECT newid(), property_id, @login_name , getdate(),'DEL'
	FROM deleted;
END;
GO


--UPDATE Trigger Creation

CREATE TRIGGER dbo.sales_audit_update
ON dbo.ASSIGNMENT
AFTER UPDATE
AS
 DECLARE @login_name VARCHAR(128)
 
    SELECT  @login_name = login_name
    FROM    sys.dm_exec_sessions
    WHERE   session_id = @@SPID
BEGIN

	 INSERT INTO dbo.PRGSaleAudit
	(audit_id, property_id, login_name, transaction_date, event_type)
    SELECT newid(), property_id, @login_name , getdate(),'UPDATE'
	FROM inserted;
END;
GO


--Creation of table that will contain the updates details

CREATE TABLE dbo.PRGSaleAudit_update(
	property_id INT NOT NULL,
	login_name VARCHAR(75),
	update_date VARCHAR(75) NOT NULL,
	old_personnel_id VARCHAR(75),
	new_personnel_id VARCHAR(75),
	old_available VARCHAR(75),
	new_available VARCHAR(75),
	old_service VARCHAR(75),
	new_service VARCHAR(75),
	old_ammount VARCHAR(75),
	new_ammount VARCHAR(75),
	old_date VARCHAR(75),
	new_date VARCHAR(75)
);


--Creation of trigger for updates details

CREATE TRIGGER dbo.update_trigger ON dbo.assignment

FOR UPDATE
AS
DECLARE @login_name VARCHAR(128)
SELECT  @login_name = login_name
FROM    sys.dm_exec_sessions
WHERE   session_id = @@SPID

INSERT INTO dbo.PRGSaleAudit_update
(property_id,
 Login_name,
 update_date,
 old_personnel_id,
 new_personnel_id,
 old_available,
 new_available,
 old_service,
 new_service,
 old_ammount,
 new_ammount,
 old_date,
 new_date
)
            SELECT assignment.property_id,
		   @login_name,
		   getdate(),
                   d.personnel_id,
                   i.personnel_id,
		   d.available,
		   i.available,
		   d.service,
		   i.service,
		   d.ammount,
		   i.ammount,
		   d.date,
		   i.date
             
            FROM dbo.assignment assignment

                 JOIN INSERTED i ON assignment.property_id = i.property_id
                 JOIN DELETED d ON assignment.property_id = d.property_id;



