Can I Increment The Column (c1+2) ---by Creating The Trigger
Mar 10, 2006
hi,
i am a beginner to ms sql server2000
i have a table
create table ddd (a int, b int)
by table structure is a b
now when i enter a value in b column suppose '2' in column b
bext time when i insert a value in the column a i have to get the value
in b as 3 is thi spossible with triggers
insert into gdg values (1,2)
a b
1 2
insert into gdg (a) values(2)
a b
2 3----------------> i have to get this 3 automatically
is there any method to get this
pls help me
satish
View 1 Replies
ADVERTISEMENT
Apr 15, 2008
Hi all,
My code below creates a trigger that fires whenever a change occurs to a 'myTable' row, but this is not what I want. I only want to log changes made to a single field called 'Charges', when that field is changed I want to log it, can anyone tell me how to modify my code to do this, thanks
Create Trigger dbo.myTrigger
ON dbo.[myTable]
FOR UPDATE
AS
Declare @now DATETIME
Set @now = getdate()
BEGIN TRY
Insert INTO dbo.myAuditTable
(RowImage,Charges,ChangeDate,ChangeUser)
SELECT 'BEFORE',Charges,@now, suser_sname()
FROM DELETED
Insert INTO dbo.myAuditTable
(RowImage,Charges,ChangeDate,ChangeUser)
SELECT 'AFTER',Charges,@now, suser_sname()
FROM INSERTED
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
View 3 Replies
View Related
Feb 23, 2013
I have the following table:
Code:
CREATE TABLE [dbo].[apcName](
[SysID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[RIN] [int] NOT NULL,
[Name] [varchar](80) NOT NULL,
[DimTab] [tinyint] NOT NULL,
[code]....
If I have an Insert trigger to set the RIN field to the next available number in the sequence of the RIN column, why does the following work for inserting 1 and only 1 record at a time but fails if doing multiple rows with a single insert?
Code:
-- Insert Trigger
UPDATE xRec
SET RIN = (Select MAX(RIN) + 1 from apcName)
FROM apcName xRec
JOIN inserted ins
ON ins.sysID = xRec.sysID
-- Works, RIN is populated correctly
Code:
Insert apcName (Name, DimTab) VALUES ('Test Name', 1)
Does Not Work. Returns 1 for all RIN's
Code:
Insert apcName (Name, DimTab) VALUES
('Test Name1', 1),
('Test Name2', 2)
View 14 Replies
View Related
Sep 25, 2007
i want to write a database trigger to increment the salary by 10% for technicians who have done three tests on a particular date.
there are two employee types.(1)technicians (2)traffic controllers.
employee category is defined in "Type" attribute of Employee table. the increment should happen only to technicians.thank you in advance.
Employee (EmployeeID,Name,Salary,Tpye)
TestEvent(TestNo,EmployeeID,TestDate)
Hussain
View 2 Replies
View Related
May 25, 2005
Hi,Please can you let me know the best solution for creating a primary key which automatically increments by 1 each time a record is added. My current Primary key is of type "Int" which increments by 1 each time, but I would like my primary key to contain "ABC" before the 1. So each time a record is added I would like to see:-ABC000001 ABC000002ABC000003Etc, EtcI am using SQL Server 2000 and creating an ASP.Net application, will I need to write code in a Stored Procedure to do this?Regards,Brett
View 4 Replies
View Related
Jan 28, 2008
Hi,
If I want to automatically insert a record which has default value in a table,
how can I create the trigger?
View 5 Replies
View Related
Nov 24, 1999
I am trying to update a new column called "sorting", with incremented values
strating at 1 for the first row and 2 for the second and 3 for the thirsd etc......., before doing that I need the table to be sorted using three
columns. I am using a cursor but it is not working I need your help.
Thanks in advance:
CODE:
set @ordering_count=0
declare review_test_cursor cursor scroll keyset for
select oid,decision_date,ranking,sorting,decision_id
from DECISION_FLAGS
order by oid,decision_date,ranking asc
open review_test_cursor
fetch next from review_test_cursor
while (@@fetch_status = 0 )
Begin
update DECISION_FLAGS
set sorting = CAST ((@ordering_count + 1) as VARCHAR)
set @ordering_count = @ordering_count + 1
fetch next from review_test_cursor
end
close review_test_cursor
deallocate review_test_cursor
View 2 Replies
View Related
May 2, 2008
How can I put an Auto increment on a non PK field?
for ordering contents items?
View 9 Replies
View Related
Oct 16, 2014
I have a column that is not set to auto increment "IDX and Im inserting 800 part numbers but i want the IDX column to start at IDX 400 and increment 1 time per part number that is inserted. how can i accomplished that task.
EXAMPLE:
IDX PART#
400 abcde
401 fghi
402 jklm
etc. and so forth until the last part# will have IDX 1200...
View 1 Replies
View Related
Oct 1, 1998
I have created a table that generates a sequential id and a
stored procedure that will return that id. The trouble is
no matter what I set the Seed or Increment values to, the
id will always start with #1 and increment by 1.
My table is BILLING_TIME_ID
Identity field BT_GEN_ID
(SEED 200, INCREMENT 1)
The sp is as follows:
CREATE PROCEDURE BT_NEXT_ID
AS
INSERT dbo.BILLING_TIME_ID DEFAULT VALUES
select count (*) from dbo.BILLING_TIME_ID
GO
I have double checked that Identity_Insert is set to off for
this table. (does this default to off unless it is set to on?) Since
there is only 1 field in the table, I don`t have any indexes set.
Thanks for your help!
Toni
View 1 Replies
View Related
Aug 22, 2006
Hi there,
With a Union all Query is there a way to have it also generate
an Auto Increment (number) column that is appended to the Union all results
?
View 2 Replies
View Related
Jun 4, 2015
I am trying to increment a column that I am creating in a select by 5's, example 5, 10, 20, 25.... first row starting at 5,
Something like
WITH CTE as
(
SELECT cast(5 as int) as myColumn from table
)
SELECT cte.myColumn + 5 from CTE
View 3 Replies
View Related
Feb 8, 2008
i've posted in the wrong forum, so im posting here
hi, im having problems to import data from my excel to a sql table.
in the excel file i have exact the same fields that i have in the table excepts the primary key which is an auto increment. When i try to import data, an error that i can't insert nulls into my auto increment column.
I put enable identity insert in the edit options, but still doesnt work.
can anyone help?
thanks in advance
View 1 Replies
View Related
Aug 29, 2007
I have a column with consist of customer number.e.g.
c001
c002
c003
How do I add the character "c" to the auto-incremental number everything I add?
View 7 Replies
View Related
Oct 27, 2006
trying to change a column that is just a INT NOT NULL column, to have an auto-increment.
Code:
ALTER TABLE [ImpExpTables].[dbo].[ImpExp_eBayLocalNeedsDeleted]
ALTER COLUMN ID int IDENTITY(1,1) NOT NULL
just gives error at IDENTITY
View 7 Replies
View Related
Jul 20, 2004
Hi,
How to Change Increment Value for existing Identity Column (MS SQL2000) ?
I know how to change the seed :
DBCC CHECKIDENT (activity, RESEED,4233596)
but I need the future id generated with step 2
4233596
4233598
4233600
I would like to do it using T-sql because I will need to do it every day after syncronising with another SQL server .
Thanks,
Natalia
View 1 Replies
View Related
Feb 18, 2014
I'm trying to create a column of numbers that increment by one.
I'm not able to use a #temptable in the application I'm using so I cannot use IDENTITY(int,1,1).
I want to add an Id column to this query:
Select distinct sd.name,ic.TABLE_SCHEMA,ic.TABLE_NAME from sys.databases sd
cross join INFORMATION_SCHEMA.COLUMNS ic
where sd.name = 'ODS1stage'
order by TABLE_SCHEMA,TABLE_NAME
How can I accomplish this without creating a temp table? I would just alter the table and insert the numbers but there are 2000 rows.
View 7 Replies
View Related
Sep 5, 2007
Dear Sir,
the following code is copied from the SQL Server Help Example.
CREATE TABLE MyCustomers2 (CustID INTEGER IDENTITY (100,1) PRIMARY KEY, CompanyName NvarChar (50))
INSERT INTO MyCustomers2 (CompanyName) VALUES ('A. Datum Corporation')
ALTER TABLE MyCustomers2 ALTER COLUMN CustId IDENTITY (200, 2)
It gives the following error.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'IDENTITY'
Can U please guide me abt the error.
with regards,
wilfi
View 5 Replies
View Related
May 28, 2015
The reason why cust_id started at #4 and not #1 is because I failed to insert property three times in a row for having "Tatoine" instead of "WI" or a state less than 5chars nchar(5) correct? Then when I did a valid statement, the row was created at the starting number of four. I imagine this prevents users from having duplicate cust_ids. This however is also where rollback and similar commands could be handy correct or is there something more obvious I'm missing on a failed "insert into" to not increment the cust_id. The three rows 1,2 and 3 do not exist I believe and are not null. Â Having null values would of contradicted the table where two columns "not null" are a requirement.
CREATE TABLE customersnew
(
cust_idINTNOT NULL IDENTITY(1,1),
cust_nameNCHAR(50)NOT NULL,
cust_addressNCHAR(50)NULL ,
cust_cityNCHAR(50)NULL ,
cust_stateNCHAR(5)NULL ,
[code]...
View 2 Replies
View Related
Mar 15, 2007
Hi members,
I am facing a grange error, I hv an asp .net application running with sql server 2000 on a web server.
one table say tblProducts, I have few colums in it with an ID Primery Key field which is auto increment.
I have around 1500 records in it, what i am seeing is that autonumbers are not comming in sequence. it is skiping few numbers after rendom intervals.
like
1
2
3
4
5
7
9
12
13
14
like this there is no error in the code as its only a simple insert query. can anybody tell me what could be the reason.
regards
Aloha
View 3 Replies
View Related
Jan 22, 2008
Hi
I am trying to use the identity data type ( column)
I am using examples from the book and management studio in sql server 2005.
I am using the identity property for the customer Id in customers table.I accidentatly executed the querry twice and I had two same record with different customer id numbers of 1 and 2 . I realised the problem and I had to delete the second record.
The problem is now even if I have deteted the 2nd record with customer id 2 , when I insert a new record the identity value ( customer Id) increments with a number after the value I deleted. i.e if I deleted a second row with customer id 2 ( identity 2) when I enter a new record it enters with a customer id of 3 and whenever i add a new record it increments from there.
So instead of first record have cust id 1 and second record customer id 2 etc , I get first record with cust id 1 , second record with cust id 3 , third record cust id 4 etc.
How can I get rid of this wrong values of identity values whenever I delete a record and try to add a new record?
Thanks
View 1 Replies
View Related
Oct 19, 2005
Hello all,I'm using SS2K on W2k.I'v got a table say, humm, "Orders" with two fields in the PK:OrderDate and CustomerID. I would like to add an "ID" column whichwould be auto-increment (and would be the new PK). But, I would reallylike to have orders with the oldest OrderDate having the smallest IDnumber and, for a same OrderDate, I'd to have the smallest CustomerIDfirst. So my question is:How could I add an auto-increment column to a table and make it createits values in a particular order (sort by OrderDate then CustomerIDhere)?In the real situation, the table I want to modify has around 500krecords and the PK has 5 fields and I want to sort on three of them.Thanks for you helpYannick
View 7 Replies
View Related
Mar 7, 2011
Due to localization I have the need to make child tables, where there is a composite Primary Key, between the Id column and the LanguageSign column. On the parent table the Id column is Identity column with auto increment.
The problem is that during the select into query to copy columns from parent to child, this auto increment behaviour of the parent-Id is copied to the child-Id. However I do not want that, because the same Id will be used by different LanguageSign entries
Is there a way to use 'select into' without copying the auto increment, or is my only option to make a whole new column without auto increment on the child and copy the records?
Â
btw I have used this statement
SET
IDENTITY_INSERT MyTable
ON , so that inserting into the Id column is possible. I can see however that this does not take away the auto increment...
View 4 Replies
View Related
Dec 28, 2007
i would to make a column contains of 3 characters and 6 auto increment numbers (example: "DLL - 123456")
and made it primary key and which data type i should use. i do not know whether i use after insert trigger in two columns one for three characters and another for code which has identity property >>>so please help me
View 4 Replies
View Related
Dec 11, 2007
Hello,
I Have a table that needs to have 2 unique number.
detail_id and detail_print_id.
detail_id is already an IDENTITY.
both fields need to be different, because when importing, it imports the same data into a table twice, with only a slight data change (and id is not one of the changes).
So I thought i could do the following:
detail_id INT NOT NULL IDENTITY(1,2),
detail_print_id INT NOT NULL IDENTITY(2,2),
--blah blah
that way, the detail_id will always be odd, and the detail_print_id will always be even. however SQL Server 2005 only allows 1 identity per table, and both these fields need to be auto generated when the field is inserted, so as to prevent double data.
is there anyway I can create a int column to auto increment, without the column being an IDENTITY??
also, I would prefer to not have to create a second table with a single column just for this work.
Thanks,
Justin
View 5 Replies
View Related
Dec 7, 2005
I want to create a trigger in SQL 2000 Enterprise, but not sure about triggers, how they work, etc. I just know that I was told I could create a trigger when new info is added to one table and check another table to see if this info is already in the other table, if not, copy the data to the other table too.
Would this be what a trigger can do for me?
what would be the best place to learn how triggers work, how to write one and install it, test it, etc?
View 2 Replies
View Related
Oct 22, 2007
I have 3 tables on my db, Projects, ProljectAllocationLog and Users
Project consists of Projectid(PK), ProjectName, UserID
ProjectAllocationLog consists of ProjectAllocationID(PK), Projectid, UserID,Date
Users consists of UserID (PK), Fullname
Over the course of time the user allocated to a project may change. The db has been set up to always show the most current user in the UserID of the Projects table,
I want to create a log that will record everytime that user has changed. (ProjectAllocationLog)
Having read through some examples posted on Forums, I believe that I can do this with a trigger, but I am not sure if I am doing it right, the trigger I have written is
Create Trigger tr_UpdateAllocationLog
ON Projects
AFTER Update
AS
If NOT UPDATE (Userid)
DECLARE @PROJECTID
DECLARE @NEWUSER
DECLARE @PREVIOUSUSER
SET @PROJECTID= (SELECT projected FROM Inserted)
SET @NEWUSER = (SELECT UserID from Inserted)
SET @ PREVIOUSUSER = (SELECT UserID from Deleted)
If @NEWUSER <> @PREVIOUSUSER
INSERT INTO ProjectAllocationLog (ProjectID, UserID, Date) VALUES (@PROJECTID, @NEWUSER, GETDATE())
Go
I would appreciate any comments
View 11 Replies
View Related
Apr 17, 2008
Hello,
I do have one table "ABC" in DB "Master" & other table "XYZ" in DB "Test".
Both tables are having same structures & same data currently. Now i want to create trigger in such a way that after every insertion & updation on table "ABC" in DB "Master" will insert & update records in table "XYZ" in DB "Test" respectively.
Can any one help me out?
Thanks
Prashant Hirani
Where Dreams Never Ends
View 7 Replies
View Related
Aug 16, 2004
I need help writing an Update trigger that puts the current date in a changed record. I understand the basic idea but can't seem to get it to work. Any help would be greatly appreciated
View 1 Replies
View Related
Jun 5, 2004
i am trying to create a trigger which when I insert a "y" on a student table in insCheck column, instructor table will create a record of the same person.
The reason is there are two tables are in my DB, student and instructor table. Student can be a instructor so whenever insCheck conlum in studnent table is checked as "y", instructor table should be populated.
Both table studentId and instructorId inserted as manually so that I am not sure how i should populate the instructor's table just fName, mI, and lName and I have go back to the instructor table and populate the rest of the table manually or is there any way to poppulate the insturctorid also while trigger is populate the name onthe instructor table.
My Two tables DDL are like down below
create table student(
studentId char(4) not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
insCheck char(1) not null,
);
create table instructor(
instructorId char(4) not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
instructorQual varchar(100) not null,
);
thanks for your help in advance!
gazawaymy
View 6 Replies
View Related
Feb 29, 2008
There are 2 tables: OrderID and Order_Details
CREATE TABLE Orders
(OrderID int
Constraint Order_ID_pk primary key
, CustomerID nchar (5)
, EmployeeID int
, OrderDate datetime
, RequiredDate datetime
, ShippedDate datetime
, ShipVia int
, Freight money
, ShipName nvarchar (40)
, ShipAddress nvarchar (60)
, ShipCity nvarchar (15)
, ShipRegion nvarchar (15)
, ShipPostalCode nvarchar (10)
, ShipCountry nvarchar (15)
)
CREATE TABLE Order_Details
(OrderID int NOT NULL
, ProductID int NOT NULL
, UnitPrice money NOT NULL
, Quantity smallint NOT NULL
, Discount real NOT NULL
, constraint Order_Details_PK
Primary key (OrderID, ProductID)
)
Create a Transact SQL procedure, customer_activity, that would, for a given CustomerID, return the number of orders that customer has made, average amount of all the customer orders, and the maximum customer’s order. The CustomerID should be the stored procedure’s input parameter. The stored procedure should use the view customer_orders.
what kind of code to create this? it says i need to use a view customer_orders, which i made:
create view customer_orders as
select orders.orderID, customerID, sum(order_details.orderID) as orderamount
from orders, order_details where orders.orderID=order_details.orderID
group by orders.orderID, customerID
but i don't know how to do it.
Next: the trigger:
Write a trigger that would, for any order entered (inserted), print the order amount as well as the customer’s average and maximum order so far, by using the view and the stored procedures created in this lab.
please and thank you
View 11 Replies
View Related
May 22, 2008
I have problem with Triggers,Iv never done it before except @school!!
One of our clients Server has same database names(WeighBridge) but Different Instances(1got Express and Other3 have SQL2005).There is a weighbridge scale on SQL Express Database.
I want to create a Trigger that Automatically updates everytime there is weighbridge scale In other Databases that have SQL2005.
If someone can help please a code or tell me what to do,
Create a Trigger on a Table ot Database??
Please Help!!!!!!
View 11 Replies
View Related
