none
how to activate a trigger in sql server 2008

    Pergunta

  • 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

    sábado, 9 de junho de 2012 13:00

Respostas

  • 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


    sábado, 9 de junho de 2012 13:28

Todas as Respostas

  • 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:22
  • 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


    sábado, 9 de junho de 2012 13:28