What is the Query of getting last 10 transaction Reports
(like insert, update, Delete Data from Tabele) ?
Answer Posted / praveen singh
CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)
INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);
drop table Employee_Test_Audit
CREATE TABLE Employee_Test_Audit
(
transid int NOT NULL IDENTITY (1,1) ,
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)
CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]
FOR INSERT
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.';
insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER INSERT trigger fired.'
GO
insert into Employee_Test values('Chris',1500);
CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test]
FOR UPDATE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
if update(Emp_Name)
set @audit_action='Updated Record -- After Update Trigger.';
if update(Emp_Sal)
set @audit_action='Updated Record -- After Update Trigger.';
insert into
Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER UPDATE Trigger fired.'
GO
update Employee_Test set Emp_Sal=1550 where Emp_ID=6
CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test]
AFTER DELETE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);
select @empid=d.Emp_ID from deleted d;
select @empname=d.Emp_Name from deleted d;
select @empsal=d.Emp_Sal from deleted d;
set @audit_action='Deleted -- After Delete Trigger.';
insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER DELETE TRIGGER fired.'
GO
CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test]
INSTEAD OF DELETE
AS
declare @emp_id int;
declare @emp_name varchar(100);
declare @emp_sal int;
select @emp_id=d.Emp_ID from deleted d;
select @emp_name=d.Emp_Name from deleted d;
select @emp_sal=d.Emp_Sal from deleted d;
BEGIN
if(@emp_sal>1200)
begin
RAISERROR('Cannot delete where salary > 1200',16,1);
ROLLBACK;
end
else
begin
delete from Employee_Test where Emp_ID=@emp_id;
COMMIT;
insert into
Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of
Delete Trigger.',getdate());
PRINT 'Record Deleted -- Instead Of Delete Trigger.'
end
END
GO
delete from Employee_Test where Emp_ID=1
select * from Employee_Test
select * from Employee_Test_Audit
ALTER TABLE Employee_Test DISABLE TRIGGER trgAfterDelete
alter table Employee_Test_Audit add column transid int NOT
NULL IDENTITY (1,1)
select top 10 * from Employee_Test_Audit order by transid
desc
Is This Answer Correct ? | 4 Yes | 3 No |
Post New Answer View All Answers
what are the important architecture components of SSRS?
What is change data capture (cdc) feature?
explain what is raid and what are different types of raid configurations? : Sql server database administration
What is the new security features added in sql server 2016? : sql server security
Explain collation?
What extended events?
How to disable stored procedure sql server?
What is sql server replication? : sql server replication
how to restart sql server in single user mode? How to start sql server in minimal configuration mode? : Sql server database administration
Difference between Logical Page and Physical Page in SSRS.
How to transfer a table from one schema to another?
How can you append an identity column to a temporary table?
What is a derived table?
Can we use custom code in ssrs?
How many types of attribute relationships are there? : sql server analysis services, ssas