Entrar
 
HomeBibliotecaWikiAprenderGaleriaDownloadsSuporte ComunidadeFóruns
Recursos para Profissionais de TI >
> 
> 
how to activate a trigger in sql server 2008

Respondido how to activate a trigger in sql server 2008

  • sábado, 9 de junho de 2012 13:00
     
      Contém Código
    Entrar para Votar
    0

    Hi all, 

    I have written my code in c#. and it uses sqlcommand builder and dataadapter.update method for updating the change back to the database. the code is given as follows

     private void con1Save_Click(object sender, RoutedEventArgs e)
        {
            con1.Open();
          
            SqlCommandBuilder cmbd1 = new SqlCommandBuilder(da1);
            cmbd1.GetUpdateCommand();
          
            SqlTransaction trans = con1.BeginTransaction();
            cmbd1.GetUpdateCommand().Transaction = trans;
            
            DataSet changes1 = new DataSet();
           
            changes1 = ds1.GetChanges();



            try
            {
                if (changes1 != null)
                {

                    da1.Update(ds1.Tables[0]);

                    trans.Commit();
                }
                ds1.AcceptChanges();
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message);

                try
                {

                    trans.Rollback();
                }
                catch (Exception e3)
                {

                    MessageBox.Show(e3.Message);


                }
            }
            finally { con1.Close(); }

        }

    Now suppose I edited the DataGrid and saved the change back to the database by clicking the button, and Let us assume that I have added new 2 rows and deleted one existing row . 

    I want to create a trigger for the occurred actions in the sqlserver 2008 . as the definition of trigger suggest  it will come after insert,delete and update .

    in this case I used a more generic update command ,which do all the functions at a time . so how I can define the trigger so that whenever a new row is added/deleted/modified it should be reflected to another table named  "RepairInfo" 

    Thanking you 

    Iqbal

    itismeiqbal

     

Todas as Respostas

  • sábado, 9 de junho de 2012 13:22
     
     
    Entrar para Votar
    0

    itismeiqbal,

    Yes you can do so in a single trigger, which will be triggered for any DML activitiy either Insert, Update or Delete and using the two magical table called INSERTED and DELETED you can achieve your tasks.

    Please let us know if you would face any other problem.

    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • sábado, 9 de junho de 2012 13:28
     
     Respondido Contém Código
    Entrar para Votar
    0

    First of all, it has been already implemented as default feature of SQL Server 2008+ Enterprise edition.  Check Change Data Capture

    If you want to do something manually - you can do something like that

    use tempdb
go

create table dbo.MyData
(
	RecID int not null identity(1,1),
	Col1 int not null,
	Col2 int not null,

	constraint PK_MyData
	primary key clustered(RecID)
)
go

create table dbo.MyDataHistory
(
	ID int not null identity(1,1),
	Action char(1) not null
		constraint CHK_MyDataHistory_Action
		check (Action in ('I','U','D')),  
	ActionTime datetime not null	
		constraint DEF_MyDataHistory
		default getutcdate(),
	RecId int not null,

	NewCol1 int null,
	NewCol2 int null,
	OldCol1 int null,
	OldCol2 int null,

	constraint PK_MyDataHistory
	primary key clustered(ID)
)
go

create index IDX_MyDataHistory_RecId
on dbo.MyDataHistory(RecId)
go

create trigger dbo.trgMyDataHistory
on dbo.MyData
after insert, update, delete
as
begin
	if @@ROWCOUNT = 0
		return
	set nocount on

	insert into dbo.MyDataHistory(Action, RecId, NewCol1, NewCol2, OldCol1, OldCol2)
		select 
			case 
				when i.RecId is not null and d.RecId is not null 
				then 'U'
				else
					case
						when i.RecId is not null
						then 'I'
						else 'D'           
					end
			end,
			coalesce(i.recid, d.recid), i.Col1, i.Col2, d.col1, d.col2
		from inserted i full outer join deleted d on
			i.RecId = d.RecId      
end
go
        

insert into dbo.MyData(Col1,Col2)
values(1,1),(2,2),(3,3)
go

update dbo.MyData
set Col1 *= 10
go

delete top (2) from dbo.MyData
go

select * from dbo.MyData
select * from dbo.MyDataHistory
go

    As the side note - This trigger would introduce 'D' and 'I' rows in case if you update PK (RecId).

    Thank you!

    My blog: http://aboutsqlserver.com