UPDATE TRIGGER ON TABLE

Trigger are three types : INSERT, UPDATE, DELETE
and trigger have two magic table  INSERTED and DELETED... 
when we execute DML (INSERT, UPDATE, DELETE) operation then trigger is automatically execute.

example : I have three table INWARD , OUTWARD  and ALLTYPESTOCK OK.
so when i insert data in "inward" table then data insert not only inward but also in "alltypestock" table. and same situation with outward... 

SO task is data of both table is store in ALLTYPESTOCK table, when i want to chage(update) any data of alltypestock then automatically data update which came form the table .
UPDATE TRIGGER:        

                                                          ------------------- INWARDSTOCK
                                                       /
Alltypestock ----- UPDATE DATA > 
                                                       \ ___________  OUTWARDSTOCK

create trigger updatetrigger
on alltypestock
for update
as
begin
        declare @srno int, @ttype nvarchar(10),@party nvarchar(10),@PayOrder
nvarchar(10),@ddate date, @dtime datetime, @getpass
nvarchar(10),@chaln_no nvarchar(10),@discription
nvarchar(10),@Quantity nvarchar(10),@Remark nvarchar(10),@username
nvarchar(10),@UIunit nvarchar(10),@JobNo nvarchar(10);
        select @srno = i.serial_no from inserted i;
        select @ttype = i.ttype from inserted i;
        select @party = i.party from inserted i;
        select @PayOrder = i.PayOrder from inserted i;
        select @ddate = i.ddate from inserted i;
        select @dtime = i.dtime from inserted i;
        select @getpass = i.getpass from inserted i;
        select @chaln_no = i.chaln_no from inserted i;
        select @discription = i.discription from inserted i;
        select @Quantity = i.Quantity from inserted i;
        select @Remark = i.Remark from inserted i;
        select @username = i.username from inserted i;
        select @UIunit = i.UIunit from inserted i;
        select @JobNo = i.JobNo from inserted i;

        IF UPDATE(serial_no)
        IF UPDATE(ttype)
        IF UPDATE(party)
        IF UPDATE(PayOrder)
        IF UPDATE(ddate)
        IF UPDATE(dtime)
        IF UPDATE(getpass)
        IF UPDATE(chaln_no)
        IF UPDATE(discription)
        IF UPDATE(Quantity)
        IF UPDATE(Remark)
        IF UPDATE(username)
        IF UPDATE(UIunit)
        IF UPDATE(JobNo)
        -- query start
                insert into alltypestock(serial_no,ttype,party,PayOrder,ddate,dtime,getpass,chaln_no,discription,Quantity,Remark,username,UIunit,JobNo)values
(@srno,@ttype,@party,@PayOrder,@ddate,@dtime,@getpass,@chaln_no,@discription,@Quantity,@Remark,@username,@UIunit,@JobNo)

                if @ttype = 'Inward'
                begin
                        update inwardstock set ttype = @ttype,  party = @party,
PayOrder=@PayOrder,ddate=@ddate,dtime=@dtime,getpass=@getpass,chaln_no=@chaln_no,discription=@discription,Quantity=@Quantity,Remark=@Remark,username=@username,UIunit=@UIunit,JobNo=@JobNo
where serial_no = @srno and ttype = 'Inward';
                        print 'inward working';
                end
                else if @ttype = 'Outward'
                begin
                        update OutwardStock set ttype = @ttype,  party = @party,
PayOrder=@PayOrder,ddate=@ddate,dtime=@dtime,getpass=@getpass,chaln_no=@chaln_no,discription=@discription,Quantity=@Quantity,Remark=@Remark,username=@username,UIunit=@UIunit,JobNo=@JobNo
where serial_no = @srno and ttype = 'Outward';
                        print 'outward working';
                end
        -- query end
end



Have you any problem so plz share with me .... I'll try to solve your troubles.   
good luck ..  ;)

 thank ...  :)

Comments

Post a Comment

Popular posts from this blog

Tree view in winforms using c#

Image
Tree view in c# ms sqlserver 2008(database) __________________________ step 1 _________ CREATE TABLE [dbo].[myTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [title] [varchar](255) NOT NULL, [parentID] [int] NULL,  CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED  ( [ID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] step 2 _________ INSERT INTO myTable(title, parentID) VALUES('Microsoft', NULL) INSERT INTO myTable( title, parentID) VALUES('C#', 1) INSERT INTO myTable( title, parentID) VALUES('VB.net', 1) INSERT INTO myTable( title, parentID) VALUES('Open Source', NULL) INSERT INTO myTable( title, parentID) VALUES('Python', 4) INSERT INTO myTable( title, parentID) VALUES('Ruby', 4) INSERT INTO myTable( title, parentID) VALUES('PHP', 4) INSERT INTO myTable( title, parentID) VALUES('Perl...
Read more

how to Replace null value with 0 Using C#

how to Replace null value with 0 Using C# //here we suppose that objDataTable have data collection as table now access this //datatable object. decimal  j = 0;  j = objDataTable.Rows[0]["Qty"] ==  DBNull .Value ? 0 :  Convert .ToDecimal(objDataTable.Rows[0]["Qty"]); NOTE :- //if   DBNull.value is null  then   "0 " else  "your code here")  // ? = then // : = else Thank You [Sumit Singh Shekhawat]
Read more

how to fetch all HTML Table Records and modify all records accordingly using jquery and Javascript

In my example, I have fetch HTML Table Records and Convert to Class List Object for C# Result: Click Here < script src = "https://ajax.googleapis.com/ajax/libs/jquery/3.6.3/jquery.min.js" ></ script > < script >   $ ( document ). ready ( function () {     // Get the table element     const table = document . getElementsByTagName ( "table" )[ 0 ];     // Get all the table rows     const rows = table . rows ;     // Create an empty array to store the table data     const data = [];     // Loop through each row and get the cell data     for ( let i = 0 ; i < rows . length ; i ++) {       const cells = rows [ i ]. cells ;       const rowData = {};       //new Columns { CharacterName = "", Char = "", Code = "", Decimal = "", Binary = "", Hex = "" },       var stringCon = "new Columns {" ;       /...
Read more