Update Trigger (Very IMP And Urgent)

Jan 10, 2001

There are two table table A and table B , if something is updtaed in
table A , the same should reflect in table B, i wrote a trigger upwhen i modify any thing table A it does not reflect in table B could any one guide me through this....

Here is the Update trigger i wrote :

CREATE TRIGGER [updbacklog] ON [testbacklog]
FOR UPDATE
AS
Update test1backlog
Set test1backlog.WorkOrderNumber = inserted.WorkOrderNumber
, test1backlog.SalesOrderNumber = inserted.SalesOrderNumber
, test1backlog.CustPONumber = inserted.CustPONumber
, test1backlog.Status = inserted.Status
, test1backlog.Comments = inserted.Comments
, test1backlog.TargetCompletionDate = inserted.TargetCompletionDate
, test1backlog.ActualCompletionDate = inserted.ActualCompletionDate
, test1backlog.IsCommercialReportRequired = inserted.IsCommercialReportRequired
, test1backlog.WorkOrderType = inserted.WorkOrderType
, test1backlog.CustomerName = inserted.CustomerName
, test1backlog.Attn = inserted.Attn
, test1backlog.CustAddr1 = inserted.CustAddr1
, test1backlog.CustAddr2 = inserted.CustAddr2
, test1backlog.CustAddr3 = inserted.CustAddr3
, test1backlog.City = inserted.City
, test1backlog.State = inserted.State
, test1backlog.Postal = inserted.Postal
, test1backlog.Customer = inserted.Customer
, test1backlog.Address = inserted.Address
, test1backlog.ShipDate = inserted.ShipDate
, test1backlog.Carrier = inserted.Carrier
, test1backlog.Waybill = inserted.Waybill
, test1backlog.CanBeShipped = inserted.CanBeShipped
, test1backlog.PlannerCode = inserted.PlannerCode
, test1backlog.rowguid = inserted.rowguid
from inserted join test1backlog on inserted.WorkOrderNumber = test1backlog.WorkOrderNumber
and test1backlog.SalesOrderNumber = inserted.SalesOrderNumber
and test1backlog.CustPONumber = inserted.CustPONumber
and test1backlog.Status = inserted.Status
and test1backlog.Comments = inserted.Comments
and test1backlog.TargetCompletionDate = inserted.TargetCompletionDate
and test1backlog.ActualCompletionDate = inserted.ActualCompletionDate
and test1backlog.IsCommercialReportRequired = inserted.IsCommercialReportRequired
and test1backlog.WorkOrderType = inserted.WorkOrderType
and test1backlog.CustomerName = inserted.CustomerName
and test1backlog.Attn = inserted.Attn
and test1backlog.CustAddr1 = inserted.CustAddr1
and test1backlog.CustAddr2 = inserted.CustAddr2
and test1backlog.CustAddr3 = inserted.CustAddr3
and test1backlog.City = inserted.City
and test1backlog.State = inserted.State
and test1backlog.Postal = inserted.Postal
and test1backlog.Customer = inserted.Customer
and test1backlog.Address = inserted.Address
and test1backlog.ShipDate = inserted.ShipDate
and test1backlog.Carrier = inserted.Carrier
and test1backlog.Waybill = inserted.Waybill
and test1backlog.CanBeShipped = inserted.CanBeShipped
and test1backlog.PlannerCode = inserted.PlannerCode
and test1backlog.rowguid = inserted.rowguid


Thanks a lot in advance.

View 6 Replies


ADVERTISEMENT

Dynamic If Update() In Trigger - Urgent!

Jul 23, 2005

Hi AllI have a question about generating dynmamicly If Update() statement in atrigger..in My db, there is a table that holds some column names of an another table.for example;Columns Table-A: Col1, Col2, Col3, Col4,Col5Table-B: Col2, Col5 (The selected columns of Table A)Then, in the Trigger of Table-A I use;Select name from syscolumns where id=object_id('Table-A')fetch next from TableA_Cursor into @strColNamethen, I used a statement like this..if UPDATE(' + @strColName + ')But it gives "incorrect syntax" error..How can I write this line?Thanks alot in advance...--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

Trouble With Update Trigger Modifying Table Which Fired Trigger

Jul 20, 2005

Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'

View 1 Replies View Related

Trigger To Update One Record On Update Of All The Tables Of Database

Jan 3, 2005

hi!

I have a big problem. If anyone can help.

I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.

I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.

But i don't know exactly how to do the coding for this?

Is there any other way to do this?

can DBCC help to retrieve this info?

Please advise me how to do this.

Thanks in advance.

Vaibhav

View 10 Replies View Related

Transact SQL :: Firing After Update Trigger - On Table Row Update

Jul 8, 2015

I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
 
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),

I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.

I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?

View 7 Replies View Related

DDL Trigger (urgent )

Mar 6, 2007

Posted - 03/05/2007 : 01:58:00
--------------------------------------------------------------------------------

Hi pals,

I need one trigger for Auditing purpose.Currently we are using SQL Server 2005.
I have gone thru some articles on DDL trigggers but iam not able to get good info.


Frns, Need a trigger which can track all ddl's on all tables in the current database in an
audit table. It should include dropping tables,altering tables ... I should'nt allow
both these activities. The action performed must be tracked in the following table.

create table audit_ddl
(
login_user varchar(20),
login_time datetime,
table_name varchar(50),
action_performed varchar(200) -- Entire ALTER/DROP SQL stmt the user issued issued must be tracked here.
)


Can any senior DBA can help me out.

Thanks & Regards,
franky

View 4 Replies View Related

Trigger(its Very Urgent)

Jul 12, 2007



































Dear Sir,

Hello I Need To Know ABout Trigger

What Is The Trigger ,and i need to Make Example On the Trigger that

I Have Two Table,Employees, Department

i nedd to make Trigger That when i make insert into the Employees Table ,

The No-of Employee Column on the Department table will increase

With My Best Regard

Khalil.T.Hamad

View 7 Replies View Related

Trigger(its Very Urgent)

Jul 12, 2007

Dear Sir,

Hello I Need To Know ABout Trigger

What Is The Trigger ,and i need to Make Example On the Trigger that

I Have Two Table,Employees, Department

i nedd to make Trigger That when i make insert into the Employees Table ,

The No-of Employee Column on the Department table will increase

With My Best Regard

Khalil.T.Hamad

View 6 Replies View Related

Trigger(its Very Urgent)

Jul 12, 2007















Dear Sir,

Hello I Need To Know ABout Trigger

What Is The Trigger ,and i need to Make Example On the Trigger that

I Have Two Table,Employees, Department

i nedd to make Trigger That when i make insert into the Employees Table ,

The No-of Employee Column on the Department table will increase

With My Best Regard

Khalil.T.Hamad

View 1 Replies View Related

Update Trigger Behaviour W/o A Trigger.

May 30, 2008

Hi,
I am not sure if this is the right forum to post this question.
I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio.
When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H".
This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table.
What else would cause the database automatically change my update?
Could there be any other place I should look for an update trigger on this table?
Thanks,

View 3 Replies View Related

Trigger To Update A Table On Insert Or Update

Feb 15, 2008



Hello

I've to write an trigger for the following action

When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz

all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated

statut_tiers to 1
and date_cloture to the same date as entered

the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture

thank you for your help
I've never done a trigger before

View 14 Replies View Related

Trigger And Replication (Urgent!!!!!!!!)

Feb 21, 2002

Hi Guys,

I have a database replicated to another database in a remote server.
We have a trigger in one of the tables in remote Database(replicated), which will fire as soon as there is an insert into this table. This trigger is maintaining the business logic. This trigger will insert the records into another table depending on what kind of information obtained from the new row(which was replicated).
Problem is, since we are using replication ( it uses bcp internaly), the trigger is not firing. I some how wanted to add these new records to the other table (automated). Can you guys please advice me on how to implement this.
I appreciate your help
Thanks,
Jeyam

View 1 Replies View Related

Trigger For The Same Table !!!!! URGENT PLEASE

Sep 17, 2006

Hi .... This is my problem .....

I have two table with the same structure (ID, DATE_1, DATE_2).... PLANS and AUD_PLANS. I need to create a trigger (FOR INSERT) that do the following things:

In PLAN table i have the next row: (1234, '01/01/2006', '30/01/2006'). I need to capture these values to compare with the row to insert. How can i do this?

I try this into de Trigger Code ....

DECLARE @FECHA_1_ANT AS datetime
DECLARE @FECHA_2_ANT AS datetime

DECLARE @FECHA_1_INS AS datetime
DECLARE @FECHA_2_INS AS datetime

Select @FECHA_1_ANT = P.DATE_1, @FECHA_2_ANT =P.DATE_2 from PLANS P inner join INSERTED I on P.ID = I.ID

But the values for @FECHA_1_ANT and @FECHA_2_ANT are the same that the values to insert.....

Please help me !!!!!

Thanks

View 3 Replies View Related

Referential Integrity Trigger---Urgent Pls

Oct 4, 2000

Hi,
I need to write a referential integrity trigger. I have two tables called Table1 and Table2. In both the tables i have common column called FieldA.
Now i need to write a trigger, whenever i updates Table1 this trigger automatically needs to update a FieldA in Table2.
Can anyone give me Code or suggestions , how to write this trigger. I am new to triggers.
Thank you!

---Ram

View 1 Replies View Related

Referential Integrity Trigger---Urgent Pls

Oct 4, 2000

Hi,
I need to write a referential integrity trigger. I have two tables called Table1 and Table2. In both the tables i have common column called FieldA.
Now i need to write a trigger, whenever i updates Table1 this trigger automatically needs to update a FieldA in Table2.
Can anyone give me Code or suggestions , how to write this trigger. I am new to triggers.
Thank you!
I know how to write a normal insert , update, delete triggers and using help from books online.. SO pls do it in useful.

---Ram

View 1 Replies View Related

Deadlock Problem In SQL Trigger Urgent

Aug 22, 2006

Hello Db experts,

Recently I am facing a problem as, I have a trigger on table1 in after insert
event. When i tried to insert data on table from 4 different source deadlock problem for causing trigger fire. Whenever I removed the trigger no dead lock occured. Can any one help me to handle this deadlock problem.

View 10 Replies View Related

Urgent Help Needed - Creating A Trigger?

Feb 16, 2007

I want a stored procedure to run everytime the batch Status field is set to 'Released'


<Code>
CREATE TRIGGER [CSITSS].[tdecker].[FB401BV_TRIGGER] ON [CSITSS].[dbo].[Fbbatch] FOR UPDATE [Status]

AS


DECLARE @RC int
DECLARE @Batch int

SET @Batch = (??? BATCH NUMBER THAT WAS SET TO RELEASE ???)

EXEC @RC = [CSITSS].[tdecker].[GLP_FB401BV_BATCH] @Batch

</Code>

View 9 Replies View Related

Update Trigger To Update Another Table

Dec 17, 2001

I have an update trigger which fires from a transactiion table to update a parent record in another table. I am getting no errors, but also no update. Any help appreciated (see script below)

create trigger tr_cmsUpdt_meds on dbo.medisp for UPDATE as

if update(pstat)
begin
update med
set REC_FLAG = 2
from deleted dt
where med.uniq_id = dt.uniq_id
and dt.pstat = 2
and dt.spec_flag = 'kop'
end

View 1 Replies View Related

UPDATE Trigger Issue When Using UPDATE

May 30, 2008

I am trying to update a fields with an UPDATE statement but I keep getting the error message when I run the query.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I have this Update trigger that I know is causing the error message because I guess it's not built to manage multi-row updates.

Can someone help me re-write it. I also tried using the WHERE p.ID = p.ID but when I do that it modifies all rows in the modifieddate column instead of just the cells/rows that I'm updating

ALTER TRIGGER [dbo].[MultitrigCA]
ON [dbo].[ProdDesc]
AFTER UPDATE
AS

SET NOCOUNT ON

IF UPDATE (codeabbreviation)
UPDATE p
sET p.ModifiedDate = GETDATE()
FROM ProdDesc AS p
WHERE p.ID = (SELECT ID FROM inserted)

View 7 Replies View Related

Update Trigger - Update Query

Jul 20, 2005

Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

INSTEAD OF UPDATE Trigger To Hold Update

Apr 3, 2008



hi!

i have a database with about 20 tables. i appended to each table a column "UpdatedOn", and i want to write a trigger to set the date of the update date in that column, using a trigger.

i want to avoid the trigger launching for the last column (UpdatedOn).

how can i detect the rows that changed, and modify only the update date/time?
i read something about TableName_Inserted and TableName_Deleted, but i would prefer to copy as generic as possible the data from there, meaning, not to write column names in my script.

another idea i thought about was to prevent the trigger executing if no other column except for UpdatedOn changed, but... i encounter some trouble, when i try to pass column name (as string) to UPDATE() function.(Error: Expecting ID or QUOTED_ID)

thank you in advance.

View 8 Replies View Related

Urgent : Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded

Aug 3, 2005

Hi all,

I have writen a Function which call's the same function it self. I'm getting the error as below.

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can any one give me a solution for this problem I have attached the function also.

CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float)
RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float)
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID
END
DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID)
INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID)
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports

RETURN
END

View 4 Replies View Related

(Urgent) Help Need With Update Statement

Jan 9, 2008

Hi,
I want the PlanId in 6 tables and is there is a way i can provide a statement...
 
Update TableName
  SET Plan_NUM = '279-072230'Where
Plan_NUM = '120003'
but i need to provide mulitple plan_NUM??
 
REgards
Karen

View 33 Replies View Related

Help With SqlDataAdapter.Update URGENT

Feb 7, 2008

 MyCommand.Parameters.Add(new SqlParameter("@ConsultantName",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Calls",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@PPC",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Mth",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@DaysInMonth",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Coach",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Center",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@ProductValue",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@ObjectiveValue",SqlDbType.VarChar));
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.CommandTimeout = 360;
try
{
SqlDataAdapter saveCenterCoaches = new SqlDataAdapter(MyCommand);
saveCenterCoaches.InsertCommand = MyCommand;

DataSet updateSet = finalSet.GetChanges(DataRowState.Added);
saveCenterCoaches.Update(updateSet.Tables[0]);
}
catch(Exception ex)
{
throw ex;
}
 Iam getting "Procedure expects parameter @ConsultantName, which was not supplied."
I have consultantname and other parameters built in my datatable.
Is it the correct way of doing?
Can someone help.It is urgent.

View 5 Replies View Related

Urgent : Update() Function

Jun 2, 2000

IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
BEGIN
DROP TABLE dbo.TestTrigger
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
PRINT '<<< FAILED DROPPING TABLE dbo.TestTrigger >>>'
ELSE
PRINT '<<< DROPPED TABLE dbo.TestTrigger >>>'
END
go
CREATE TABLE dbo.TestTrigger
(
colA int NULL,
colB int NULL
)
go
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.TestTrigger >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.TestTrigger >>>'
go


IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
BEGIN
DROP TRIGGER dbo.TestTrigger_i
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
PRINT '<<< FAILED DROPPING TRIGGER dbo.TestTrigger_i >>>'
ELSE
PRINT '<<< DROPPED TRIGGER dbo.TestTrigger_i >>>'
END
go
CREATE TRIGGER dbo.TestTrigger_i
ON dbo.TestTrigger
FOR INSERT AS

IF UPDATE(colA)
select "updating col A"

IF UPDATE(colB)
select "updating col B"

go
go
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
PRINT '<<< CREATED TRIGGER dbo.TestTrigger_i >>>'
ELSE
PRINT '<<< FAILED CREATING TRIGGER dbo.TestTrigger_i >>>'
go



insert into TestTrigger
(colA)
values
(1)
go

insert into TestTrigger
(colA, colB)
values
(2,3)
go

View 1 Replies View Related

Update Query (Urgent Please)

Apr 12, 2005

Hi Guys,
I have a table created like
(cname,perfweek,orderstaken,callsmade).
the application give a report of perticular salespersons(cname) performance by ordertaken and callsmade.
somedays back, they have entered different cname for the same person. What I wanted to do is, when they give me correct cname and the wrong cname, I will have to findout in a perticular week, is there are duplication of cname and if then I have to add orderstaken and callsmade to the correct Cname for that particular week and after that delete the wrong Cname detail for that particular week(because it is added to the correct cname rows for that week).
And then I have to change the wrong Cname to the correct Cname for all the rows, if there is no data found for right cname matching for that week.
cname perfweek orderstaken callsmade
----- ------- --------- --------
abc 1 3 4
bbb 1 5 6
abc 3 3 1
bbb 3 2 3
bbb 2 4 5

in this eg: abc is the right cname and bbb is the wrong cname. Here what I have to do is I have to combine rows for the correct and wrong cname for that particular week ie: now the table looks like :
cname perfweek orderstaken callsmade
---- ------- ---------- ----------
abc 1 8 10
bbb 1 5 6
abc 3 5 4
bbb 3 2 3
bbb 2 4 5
(note: after combine, I have to delete the bbb for the perfweek of 1 and 3)
and then I have to update the rest of the wrong cname to the correct cname.
Then the firnal table looks like

cname perfweek orderstaken callsmade
----- ------- --------- --------
abc 1 8 10
abc 3 5 4
abc 2 4 5

It is an urgent requirment and I will really appreciate your valuable inputs.
Thanks very very much.

View 1 Replies View Related

Need Help With Update Link In FormView - URGENT

Dec 7, 2006

Hi Folks,
 Somehow i am stuck at a very basic step. I have two pages  -
1. DomainList.aspx which just displays all the records from the Domains table.2. DomainAddEdit.aspx which displays the selected record in FormView(Edit Mode) with two link for Update and Cancel.
The Update link in the FormView does nothing on the first click. It just reloads the page with the new data I entered. If I click again on the Update link, it throws me an error:
"Cannot insert the value NULL into column 'DNS', table 'MSInteractive.dbo.Domains'; column does not allow nulls. UPDATE fails.The statement has been terminated. "
I have no clue why all this is happening. I have spent more than two days on this and this is very very frustrating.
Just to mention, I haven't written any code for this. Its developed all using the VWD tools available. I have posted this message earlier but haven't got any response. I am sure most of you guys must have been doing these steps everyday. So, please post your thoughts.
 Thanks a million.
Here is the relevant code for my DomainADDEdit.aspx:
<asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1" DefaultMode="Edit">        <EditItemTemplate>            Id:            <asp:TextBox ID="IdTextBox" runat="server" Text='<%# Bind("Id") %>'></asp:TextBox><br />            RegistrarAccountId:            <asp:TextBox ID="RegistrarAccountIdTextBox" runat="server" Text='<%# Bind("RegistrarAccountId") %>'></asp:TextBox><br />            Registrar:            <asp:TextBox ID="RegistrarTextBox" runat="server" Text='<%# Bind("Registrar") %>'></asp:TextBox><br />            DNS:            <asp:TextBox ID="DNSTextBox" runat="server" AutoPostBack="True" OnTextChanged="DNSTextBox_TextChanged"                Text='<%# Bind("DNS") %>'></asp:TextBox><br />            EmailHost:            <asp:TextBox ID="EmailHostTextBox" runat="server" Text='<%# Bind("EmailHost") %>'></asp:TextBox><br />            Registered:            <asp:TextBox ID="RegisteredTextBox" runat="server" Text='<%# Bind("Registered") %>'></asp:TextBox><br />            Expires:            <asp:TextBox ID="ExpiresTextBox" runat="server" Text='<%# Bind("Expires") %>'></asp:TextBox><br />            MsiResponsible:            <asp:CheckBox ID="MsiResponsibleCheckBox" runat="server" Checked='<%# Bind("MsiResponsible") %>' />    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MSInteractiveConnectionString %>"        SelectCommand="SELECT [Id], [RegistrarAccountId], [Registrar], [DNS], [EmailHost], [Registered], [Expires], [MsiResponsible] FROM [Domains] WHERE ([Id] = @Id)"        UpdateCommand="UPDATE Domains SET DNS = @txtDNS WHERE (Id = @Id)">        <UpdateParameters>            <asp:FormParameter FormField="DNSTextBox" Name="txtDNS" />            <asp:QueryStringParameter Name="Id" QueryStringField="Id" />        </UpdateParameters>        <SelectParameters>            <asp:QueryStringParameter Name="Id" QueryStringField="Id" Type="String" />        </SelectParameters>    </asp:SqlDataSource><br />            <asp:LinkButton ID="UpdateButton" runat="server" CommandName="Update"                Text="Update" OnClick="UpdateButton_Click"></asp:LinkButton>            <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"                Text="Cancel"></asp:LinkButton>        </EditItemTemplate>
 

View 5 Replies View Related

(URgent) My Update Statement Is Not Working..

Dec 17, 2007

Hi..
I have inserted couple of data in a particular table which is as follows..
Portfolio Table
PortId PlanId  PortfolioName                           PorfolioDescription  ClientPortolioId




771
17838
BALPORT                                           
NULL
NULL

772
17838
HIGHGROW                                          
NULL
NULL

773
17838
MODGROW                                           
NULL
NULLMy FundDBF is as follows
 RowNumber  FUND_ID            f.ASSETDESC                    Import




20
BALPORT        
              
Balanced                                      
True

21
MODGROW        
              
Moderate Growth                               
True

22
HIGHGROW       
              
High Growth                                   
True
and this is my Update statement UPDATE
Statements..PlanPortfolio
SET
PlanId = pm.PlanId,
PortfolioName = pm.FUND_ID,
PortfolioDescription = pm.ASSETDESC
FROM
Statements..PlanPortfolio p
Join (
SELECT DISTINCT
p.PlanId,
pd.FUND_ID,
f.ASSETDESC ---pd.FUND_ID
FROM
PartDBF pd
INNER JOIN Statements..ClientPlan p
on pd.PLAN_NUM = p.ClientPlanId
INNER JOIN FundDBF f
on pd.FUND_ID = f.FUND_ID
WHERE
pd.Import = 1
AND NOT (
pd.FUND_ID IS NULL
OR
Len(pd.FUND_ID) = 0
OR
pd.FUND_ID NOT IN (
SELECT
PortfolioName
FROM
Statements..PlanPortfolio pp
Where
pp.PlanId = p.PlanId
)
)
) pm
on p.PlanId = pm.PlanId
 
I am trying to put the above table with f,AssetDesc in the PorfolioDescription field..
Any help will be appreciated..
Regards
Karen

View 17 Replies View Related

SQL Server Update Query Help -- URGENT!!!

Oct 10, 2005

Hello all. I have 2 tables members1 and members2.
members1 have a field called directory_services_idmembers2 also has a field directory_services_id and another one called employee_id
 I need to update directory_services_id in members1 to the value employee_id in members2 Where members1.directory_services_id = members2.directory_services_id I dont want to update all the records. Only those records in members1 that have a match on directory_services_id in members2. So if there are 100 records that match on directory_services_id then i want to update only those 100 and not all the records.This is the query that I have so far.Update members1 M1 Set directory_services_id = (Select member_custom20 From members2 M2 Where M1.directory_services_id = M2.directory_services_id)Where M1.directory_services_id IN (Select directory_services_id From M2)And the error I am getting isServer: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'M1'.Server: Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'Where'.Please help. Thank you.

View 2 Replies View Related

Column Update . Urgent Help Required Pl. !!

Jan 11, 2002

Hi ...

There is a strange problem on my tables . When i execute a simple query from Query Analyzer, say select * from ' table name' , then i can edit or enter text in my result in one of the columns.

But the same table if i try to open through the Enterprise problem , i.e selecting the table , right click and select open table and then if i select return all rows , it does not allow me to enter text in a specific coloumn .

But the same is possible if i execute a query and try to enter text in the results which is displayed in grid from the Query Analyser .

Could anybody help me to know what could be the problem and how can i enter a text it ?

Many Thanks !!

View 2 Replies View Related

Bulk Insert W/Update (urgent)

Aug 11, 2006

[EDIT #2]
Using this query:


Code:

INSERT INTO Users (userName, UserSalt, UserHash1, UserHash2, UT_memberID)
select memberFirstName + '.' + memberLastName + '56' as userName, '{AxxxxxDE-6xx6-4xxD-Bxx9-3xxxx79xxxxE}',
'{4xxxxxx6-8xx5-6xxD-Cxx6-4xxxFxxx1xx9}', '{0xxx8xxE-Cxx4-6xx8-ExxB-Dxxxx4xxx2xC}', members.memberID
From members
Inner Join groupLeaders ON members.memberID = groupLeaders.memberID
SELECT @@Identity AS UserID



How can I modify the portion that is inserting the '56' at the end of each username to do the following:

1) check to see if username already exists in the database (using a query with "LIKE %'")

2) if not, create the username "as-is" or how it should be without the number

3) if already exists, get a count of records matching your search criteria .... now make a new username + + (count + 1).ToString();

Any thoughts... I am struggling to put these two pieces together.

Thanks,

Zoop



[EDIT - original post below this]

I have modified my method to make this a bit easier. I added a memberID field to my [Users] table so that I can update my [Members] table in a difference statement after the insert takes place.

I have the following query, and it completes succesfully in query analyzer (though I haven't actually executed the SP, just testing the syntax...) anyway, here is what I have:


Code:

INSERT INTO Users (userName, UserSalt, UserHash1, UserHash2, UT_memberID)
select memberFirstName + '.' + memberLastName + '56' as userName, '{AxxxxxDE-6xx6-4xxD-Bxx9-3xxxx79xxxxE}',
'{4xxxxxx6-8xx5-6xxD-Cxx6-4xxxFxxx1xx9}', '{0xxx8xxE-Cxx4-6xx8-ExxB-Dxxxx4xxx2xC}', members.memberID
From members
Inner Join groupLeaders ON members.memberID = groupLeaders.memberID
SELECT @@Identity AS UserID



I am hoping this will create a user for all members whose 'memberID' can be found in the groupLeaders table... is this correct?

Also, notice the 56 being appended to the end of each username. I would like this to be a random number generated within a given range... can this be done? any advice?

Thanks,

Zoop


[Original post below - provide more background]


I have three tables involved with this insert/update:

[Members]
-memberID
-memberFirstName
-memberLastName
-UserID

[GroupLeaders]
-groupLeaderID
-memberID

[Users]
-UserID
-Username
-UserSalt
-UserHash1
-UserHash2

I want to insert into the [Users] table the memberFirstName.memberLastName + randomNum into the 'UserName' column from the [Members] table. Also, I want to make all passwords the same, in this case I know the Salt, Hash1, Hash2 I will be using and would like to pass these in for the 'UserHash1' 'UserHash2' fields.

Now, I only want to make this insert where the memberID is in the GroupLeaders table. and Finally, I need to Update my Members table with a UserID where the memberID matches the one used from the groupLeaders table.

Does anyone have any ideas on how I can accomplish this, even if it requires adding a temporary field to one of my tables... here is what I have so far, but am recieving errors and can't quite figure this one out. (btw - I also don't know how to gen the rand num and was using the literal 23 as a placeholder.) Thanks...


Code:


INSERT INTO Users (userName, UserSalt, UserHash1, UserHash2)
select a.memberFirstName + '.' + a.memberLastName + '23' + as userName, '{AA99FCDE-6E06-437D-B9E9-3E3D27955C3E}',
'{7xxxxxx2-4xx6-9xx1-7xx9-4x3xx4Axxx59}', '{0xx8xxE-Cxx4-6xxx-xxxx-Fxx3xxxx3xxF}', b.memberID as newMemID
From members a, groupLeaders b
Where a.memberID = b.memberID
SELECT @@Identity AS UserID

Update Members Set UserID = Ident_Current('Users')
where memberID = newMemID



Any help is appreciated!

View 2 Replies View Related

Update Trigger

Apr 6, 2004

Hello All,
I've been adviced without much detail that the best way to handle updates is with an Update Trigger to write to an AuditTable.
I always thought it was better to avoid triggers.
I'm getting confused very fast in reading about triggers.
Could someone please point me in the right direction?

Thank you,
Tina

View 6 Replies View Related

UPDATE Trigger For PK

Dec 2, 2004

i am using address table to store the address of employees, agents, students; so can't use cascade update and delete. so i decided to use trigger but i got stuck here as well.
Here employeeid is the PK of employee id. so when it is updated, i want to update that id in corresponding address table as well.
so what i need is to find out the previous value of EmployeeID before updating to use in WHERE clause and new EmployeeID to use in SET clause.

<code>
CREATE TRIGGER [AddressUpdate] ON [dbo].[MCS_Employee]
FOR UPDATE
AS
IF UPDATE(EmployeeID)
BEGIN
UPDATE MCS_Address SET EmployeeID = 'i am stuck here; how to know the previous value?
WHERE EmployeeID = ???
END
</end>

ironically, PK in my table can be updateable as well.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved