Trigger To Work Depending On Datatype And Column Name
Jan 24, 2008
Hi,
I have a trigger that fires on update, populating a varchar field [ExampleDate_Str] with the dd/mm/yy format of the inserted datetime field [ExampleDate]. It works, but I don't want to rewrite it for the thousands of datetime fields in hundreds of tables in my db. So I am looking for a way to do something like this in a trigger -
for each column in triggered tableset @ColName = (the column name)if datatype(@ColName) = datetimeset @ColName + "_Str" = convert(varchar(8),ExampleDate,3)next
Obviously the above doesn't work in SQL or indeed any other language yet invented - is it possible to make it so that it does, and if so, how? The main points are to read the column name into a variable, check the datatype, and modify the column named @ColName + "_Str".
Thanks...
View 7 Replies
ADVERTISEMENT
Apr 23, 2008
Would like to have Identity Inserted wrt a date column
Eg:
ProdDate ID Details
============================
2008.04.01 1 afafafaf
2008.04.01 2 GAFSGHFGF
2008.04.02 1 GAGJAGSDH
2008.04.02 2 QYTYTT
2008.04.03 3 QYTWRRT
At present it is not an Auto increment , I check for the Maximum ID and add One to the next Inserted record. Is there any simpler method?
View 13 Replies
View Related
May 20, 2006
Hi all, I am trying to sum a column into different variables depending on another column. Let me explain my self better with an example
DECLARE @Initial decimal(18,2), @incomings decimal(18,2), @outgoings decimal(18,2)
SELECT
@initial = CASE WHEN type = 1 THEN SUM(amount) END,
@incomings = CASE WHEN type = 2 THEN SUM(amount) END,
@outgoings = CASE WHEN type = 3 THEN SUM(amount) END,
FROM Transactions
WHERE date = '05/14/2006' AND STATION = 'apuyinc'
GROUP BY type, amount
What I am trying to do is to sum all of the incomings transactions into @incomings, all of the outgoing transactions into @outgoings and the initial transaction into @initial where
The incoming transactions is type 2,
outgoing transactions is type 3
Thanks for the help
@puy
View 5 Replies
View Related
Sep 26, 2007
Hi all,
I have a table with artikels and count, sample:
Art Count
------------
12A 3
54G 2
54A 4
I would like to query this table and for each 'count' retrieve one row:
query result:
Art Count
------------
12A 3
12A 3
12A 3
54G 2
54G 2
54A 4
54A 4
54A 4
54A 4
Is this possible?
Thanks, Perry
View 3 Replies
View Related
Jul 27, 2004
Hi there,
Is there a quick way to list all the tables in a DB that contain a certain column name?
Thanks
S
View 5 Replies
View Related
Oct 19, 2007
I´m wondering how to solve the following scenario with SSIS
I have a CITY table and a STATE table, I have to load a file with the information regarding to the CITY:
the state table is like this:
StateCode(PK) stateLegalCode stateName
============= ============== =========
1 01 Florida
the city table is like this:
citycode(PK) cityLegalCode cityname StateCode(FK)
============ ============= ======== =============
1 1001 Quakertown 1
the file has the following information
cityLegalCode cityName
============= ========
01-1001 Quakertown
...
how can I load the file into CITY table:
1-) with the file's cityLegalCode I have to split the string and if the two initial digits are 01 the registry must have 1 in the StateCode(FK).
how can I do something like that using SSIS???
thanks
View 3 Replies
View Related
May 19, 2015
I am loading files with same format from two different locations in to one database. First, my SSIS connects to location 1.
Lets Say
Location1 : C:LoadFilesImport
Location2 : D:LoadFilesImport
Location one has three different set files starting with
First Set: AP_1, AP_2,AP_3,
Second Set: VD_1, VD_2, VD_3,
Third Set: BK_1,BK_2,BK_3,
This SSIS set to run every 3 hours, if it finds files of any set load them. While loading it has to insert into a derivedcolumn called CustID. If the file name Starts with AP_ , custiD values should be as 101
AP_1 goes to Table1
AP_2 goes to Table2
AP_3 goes to Table3
If the file name Starts with VD_ , custiD values should be as 201
If the file name Starts with BK_ , custiD values should be as 301
after processing all these files in first location, the SSIS looks for files in second location and does the same?
--How to achieve CustID depending upon file name ?
View 0 Replies
View Related
Dec 15, 2003
I tryed to update tables part of my MSDE database, using the SqlDataAdapter.Update() method. It worked fine untill I tryed to update a table that has a Column with the Text SQL DataType. It didn't work. The error was :
"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."
Is there a way to do it ?
Thanks,
Jeff
View 4 Replies
View Related
May 11, 2007
Hi,
I'm not sure if this can even be done but I will ask anyway.
I have a table that has a sales price field in it. I have another field in that table called commission. The commission is 15% of the sale price. Is there any SQL that will calculate and automatically put that value in it, or only allow you to put the correct value in?
Hope this makes sense and hope someone can help.
Thanks for your time.
Liam
(Using MS SQL by the way)
View 5 Replies
View Related
Dec 12, 2005
Friends,I would just like to know that why SQL Server doen't allow us to definea text data type local variable while creating trigger?I tried creating a text variable in a trigger as a local variable andit raises error."Implicit conversion from data type text to nvarchar is not allowed.Use the CONVERT function to run this query".For this i have to use convert function in MS SQL Server.-ThanksBhavin Vyas
View 1 Replies
View Related
Feb 28, 2006
why does this trigger not work? i'm winging this and hoping for some input. nobody's responded to my earlier posts. I get no errors, but no update either.
CREATE TRIGGER UpdateLineTotalON CartItemsFOR UPDATEASDECLARE @lineTotal Numeric(9)DECLARE @itemSell Numeric(9)DECLARE @pounds Numeric(9)SELECT * FROM CartItemsSET @lineTotal = @pounds * @itemSell
View 3 Replies
View Related
Dec 1, 2004
I have the following delete trigger but it doesn't work.
ALTER TRIGGER Users_DeleteUsers
ON dbo.Users
FOR DELETE
AS
DELETE FROM InstantForum_Members WHERE MemberID IN (SELECT ForumMemberId from Deleted)
DELETE FROM InstantKB_Users WHERE UserID IN (SELECT KBMemberId from Deleted)
But when I delete a user from Users table, I get an error in this trigger saying no commit or rollback given in trigger.
Can someone tell me why this trigger will fail ?
View 2 Replies
View Related
Mar 31, 2003
Hi,
I wanted to create a new trigger, but Enterprise Manager tells me about an "Incorrect syntax near @UpdatedByID, line 28". I double-checked everything, but it still does not work :mad: .
Any hints :confused: ?
TIA,
-Gernot
Here is the statement (line 28 is marked with ***):
CREATE TRIGGER TransferToABII ON [dbo].[CALGeneral]
FOR INSERT
AS
BEGIN TRANSACTION
BEGIN
DECLARE @Event varchar(255),
@BBaseUID int,
@StartDate smalldatetime,
@EndDate smalldatetime,
@Details varchar(255),
@AddressID int,
@ProjectID int,
@UpdatedByID int,
@ActID int,
@EventID int
SELECT @Event = Event,
@BBaseUID = BBaseUID,
@StartDate = StartDate,
@EndDate = EndDate,
@Details = Details,
@AddressID = AddressID,
@UpdatedByID = UpdatedBy,
@ProjectID = ProjectID
FROM INSERTED
BEGIN
EXEC BrainBase.dbo.BB_NEW_CREATE_NoteTask_Ret *** (@UpdatedByID,
@AddressID,
@ProjectID,
@BBaseUID,
@StartDate,
GetDate(),
@Event,
NULL,
NULL,
NULL,
NULL,
@Details text,
@ActID = @ActID OUTPUT,
@EventID = @EventID OUTPUT)
END
BEGIN
UPDATE CALGeneral SET ActID = @ActID WHERE ID = INSERTED.ID
END
END
IF @@ERROR <> 0
BEGIN
RAISERROR('Error occured',16,1)
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION
View 4 Replies
View Related
Jan 18, 2006
Hi all, I have a problem with this trigger. It seams to be very simple, but it doesn't work...
I created a trigger on a table and I would want that this one updates a field of a table on a diffrent DB (Intranet). When I test it normally (a real situation), it doesn't work, but when I do an explicit update ("UPDATE AccesCard SET LastMove = getDate();" by example) it works.
If anyone could help me, I would appreciate.
NB: Is there a special way, in a trigger, to update a table when the table to update is on another BD ?
Francois
This is the trigger:
------------------------------------------------------------
ALTER TRIGGER UStatus
ON AccesCard
AFTER UPDATE, INSERT
AS
DECLARE @noPerson int
SET NOCOUNT OFF
IF UPDATE(LastMove)
BEGIN
SELECT @noPerson = Person FROM INSERTED
UPDATE Intranet.dbo._Users SET Intranet.dbo._Users.status = 1 WHERE personNo = @noPerson;
END
SET NOCOUNT ON
View 5 Replies
View Related
Mar 28, 2008
hi eveyrone,
I have an after insert trigger that works in sql2000 but not in sql2005. Can you please help me!!
CREATE trigger dbo.trUPS_tbl_I
on dbo.UPS_tbl
After Insert --For insert
as
declare @Tracking varchar(800),
@UPSID varchar(10),
@Cmmd varchar(800)
select @UPSID = cast(inserted.UPSID as varchar)
from inserted
commit
set @Cmmd = 'c:TasksUpdTrackingUpdateTrackingCS ' + @UPSID + ' ' + 'UPS1'
EXEC master.dbo.xp_cmdshell @Cmmd
The UpdateTrackingCS program will call a stored procedure to get the inserted data and update other databases. And the reason to put the commit statement in sql2000 is to have sql commit the transaction, so the store procedure in UpdateTrackingCS can query thtat inserted records.
When I use the same code in SQL2005, no matter what I do the UpdateTrackingCS program cannot query the data by the UPSID. It always says record not found.
I have tried to change commit to begin trans ... commit trans. But nothing works. Please help!
Thanks in advance.
lyw
View 1 Replies
View Related
Mar 28, 2008
hi eveyrone,
I have an after insert trigger that works in sql2000 but not in sql2005. Can you please help me!!
CREATE trigger dbo.trUPS_tbl_I
on dbo.UPS_tbl
After Insert --For insert
as
declare @Tracking varchar(800),
@UPSID varchar(10),
@Cmmd varchar(800)
select @UPSID = cast(inserted.UPSID as varchar)
from inserted
commit
set @Cmmd = 'c:TasksUpdTrackingUpdateTrackingCS ' + @UPSID + ' ' + 'UPS1'
EXEC master.dbo.xp_cmdshell @Cmmd
The UpdateTrackingCS program will call a stored procedure to get the inserted data and update other databases. And the reason to put the commit statement in sql2000 is to have sql commit the transaction, so the store procedure in UpdateTrackingCS can query thtat inserted records.
When I use the same code in SQL2005, no matter what I do the UpdateTrackingCS program cannot query the data by the UPSID. It always says record not found.
I have tried to change commit to begin trans ... commit trans. But nothing works. Please help!
Thanks in advance.
lyw
View 8 Replies
View Related
Oct 3, 2007
Hi Everyone,
I'm having some trouble with the below trigger. When I add a row of data either manually or using an INSERT query, it just doesn't do anything. It doesn't provide any error messages either. This makes me think that it's aborting the operation because rowsAffected are 0 or some other simple error. My row manipulation code could be suspect also. This is my first time writing a trigger or using T-SQL for that matter.
What I'm trying to do is to have the trigger add +1 to the Iter field of all rows where BouleID is equal to the BouleID of the row being inserted. So let's say I have the following table:
BouleID CurrentLocation Iter
A01 Inventory 1
A01 Cutting 0
A01 WIP 2
B01 WIP 0
B02 WIP 1
B02 Inventory 0
Now, if I insert a row with BouleID = A01 and Current Location = Polishing, I want the Iter field of all previous rows to iterate by +1 and this new row to have Iter = 0.
I am using SQL Management Studio Express, and SQL Server Express.
Any thoughts on anything wrong with my selection code and iteration code? Could I adapt this to handle more than one row by using a GROUP BY BouleID somehow?
Code Block
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: <this trigger will iterate the Iter field by one for each rows that has the BouleID matching the one of
-- the row being inserted>
-- =============================================
CREATE TRIGGER dbo.trgCG_DispoIterate$InsertTrigger
ON dbo.dbCG_Disposition
AFTER INSERT AS
BEGIN
DECLARE @rowsAffected int,
@msg varchar(2000), --for error message
@BouleID varchar(6) -- and do I need to enter more than one?
SET @rowsAffected = @@rowcount
IF (@rowsAffected = 0 or @rowsAffected > 1 ) RETURN --don't continue if no rows changed or doing more than one at a time.
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
SET ROWCOUNT 0
BEGIN TRY
--VALIDATION BLOCK Leave this alone for now
SELECT @BouleID = BouleID FROM Inserted --sets @bouleID equal to the BouleID of the row being inserted.
UPDATE dbo.dbCG_Disposition --This block sets the Iter field to it's previous value +1
SET Iter = ( Iter + 1 ) --
WHERE BouleID = @BouleID --
END TRY
BEGIN CATCH
IF @@TRANCOUNT >0
ROLLBACK TRANSACTION
--or this will get rolled back
EXECUTE dbo.ErrorLog$Insert -- this function creates an errorlog table which gets filled up if there is an error in the try block.
DECLARE @ERROR_MESSAGE nvarchar(4000)
SET @ERROR_MESSAGE = ERROR_MESSAGE()
RAISERROR (@ERROR_MESSAGE, 16, 1)
END CATCH
View 6 Replies
View Related
May 6, 2008
OK I am really going nuts here. I have a trigger that will work if I insert a row using SQL MGMT Studio or VS DB explorer, but it will not insert using the tableadapter.update() command.
What is also strange is that I have to execute the query in the VS DB Explorer or SSMSE to get them to fill in the default values (trigger).
No matter what they don't work in the debug application:
Below is the trigger:
Code Snippet
ALTER TRIGGER [dbo].[weight_train_log_InsertTrigger]
ON [dbo].[weight_train_log]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
UPDATE [weight_train_log]
SET [CreationDate] = GETDATE()
FROM inserted
WHERE inserted.[GUID] = [weight_train_log].[GUID]
END
View 1 Replies
View Related
Jul 23, 2005
I have a table with a column defined thus: LOCNumber Varchar(100) in atable called Bookdata.If I execute ALTER TABLE BookData ALTER COLUMN LOCNumber varchar(100)on the table, does any work get done on the table and column?Or does SqlServer know that the column is already varchar(100) andnothingneeds to be done?If it does do some work, how do you tell it not to run this as it isalready a varchar(100) column. Right now my program just blindly doesthis ALTER TABLEstatement regardless of any conditions. Is this a potential problem?Thanks for any help.
View 2 Replies
View Related
Jan 9, 2005
Hi folks, need ur help.
I want to convert strings from a textfile and convert em to appropriate datatype for the destination columns, eg if i have a source value in string 11111 and the destination column is MONEY. I want to use CONVERT(functionretruningdatatype,'11111') in my insert statement.
Any guidance!
Howdy!
View 1 Replies
View Related
Jul 3, 2004
I want get DataType of Column in Table
ex: nvarchar, varchar, etc...
Help me !!
View 2 Replies
View Related
Dec 12, 2005
I've changed the width of a column from 128 to 64. How to EM shows 64, while sysobjects and sp_help show 128 ?? Even after disconnecting and re-connecting (just to be extra sure).
View 2 Replies
View Related
Dec 2, 2006
Hello I am having a table
table1
col1 (bit)
and i want to changethe col1 type for smallint
col1 (smallint)
true will be = 1
and false = 0
how can i do it ??
thank you
View 3 Replies
View Related
Feb 12, 2014
I'm using this query to sum the values. The cost column is a float datatype and what could I differently do here to sum the cost. I'm unable to sum the cost.
Also is there any way I change the datatype to int for Cost column without losing the data.
select ID, MAX(Date) Date, SUM(Cost) Cost, MAX(Funding) Funding from Application
group by ID
View 4 Replies
View Related
May 9, 2008
Hi,
I have a data flow task where I have mutiple columns (of different data types) , I want to create a single column using all these columns in XML format and load it into a table with a column of datatype XML. Can I accomplish this using Derived column tranformation?
Thanks
View 17 Replies
View Related
Jan 21, 2007
hi ,How can I find sql Datatype (like NVARCHAR , DESIMAL & .. ) and size of columns and also is it Identity or not in a Table of SQl server?
View 4 Replies
View Related
Jul 30, 2007
Hello,How do I change the datatype of a column in a CSV file. Preferably in the select statement (apparently Cast, Convert, & Replace functions don't work when selecting from a CSV). I have a page where users upload their CSV files to and then I use SQLBulkCopy to insert all the records into my sql table. The problem is with columns of money data. EX: "$2,000" >> 2000The CSV file interprets the "$2,000" as a string of text. How do I convert it to a decimal so I can insert it into my sql database?
View 4 Replies
View Related
Mar 12, 2008
Hi all
A table consists of a column with name "Createddate" with datatype smalldatetime.
defaultly iam binding getdate() to Createddateso it is storing in tthe below format --
Mar 12 2008 11:38AM
while retreving iam mentioning...
select * from tbl where convert(varchar,createddate,101) = '03/12/2008'
it is showing no records ..even though records r there with --march 12 2008.
plz do needful in this senario.
thanks & regards
kanth
View 2 Replies
View Related
Aug 7, 2004
Hi everyone....
I need a column in my table that its DataType should be as integer arrays how can I implemant it in my sqlserver table.
Any help appreciated.
View 5 Replies
View Related
Oct 27, 2003
Hi guys,
Is there any way or method to CHANGE the DATATYPE of a column in a published table being used for transactional replication (MSSQL 2000), WITHOUT DROPPING THE SUBSCRIPTION ????
Im stuck in this mess and do have the option to drop the subscription, alter the table, create the subscription and rerun the snapshot or to recreate it by Manual Synchronisation either.
Can anyone help? Has anyone been across this dilemma before and have troubleshooted the problem? If yes, help is much appreciated.
MY PROBLEM:
~~~~~~~~~~~~~
'MyTable' is currently being published and has subscriptions to it. The PRIMARY KEY column 'id' has an Identity property as well. 'id' is of datatype smallint, however because of bad planning, i now need to change that datatype to an integer to support a larger range WITHOUT DROPPING SUBSCRIPTIONS.
I CANT DROP THE COLUMN EITHER AS IT IS BEING THE PRIMARY KEY COLUMN.
IS THERE ANY OTHER WAY I CAN DO TO ARCHIEVE MY GOAL? THANKYOU.
View 3 Replies
View Related
Jul 22, 2006
Hi,
How do we find the "column name" and "data type" of all the columns in a table. Assuming that I know the Table name or Table Object ID. I am using Microsoft SQL Server 2000.
Thanks
-Sudhakar
View 3 Replies
View Related
Oct 15, 2015
I am trying to make the following update: All the columns are fine except for the 'name' column. datatype for 'name' column in the target table is varchar(30) and the datatype for 'name' in the sourcetable is varchar(40), I cannot change the datatype of the column 'name' to varchar(40) because I am told it may affect performance. what I want to do is just update the first 'name' column of the target table by the first 30 characters of the source table column 'name'
I am using the following query, is it possible to do it or are there any other ways I can update the column without changing the datatype?
MERGE INTO [S].[dbo].[AF_Copy] AS TargetTable
USING (SELECT source_code, name, addr1, city, zip FROM
[D].[D_TEST].[dbo].[SO_Copy]) AS SourceTable
ON ([TargetTable].[Code] = [SourceTable].[source_code])
[code]...
View 3 Replies
View Related
Dec 20, 2013
I am having Test table with (id int , DataDescription xml) . I need to read the values from DataDescription column and insert into one table.
View 2 Replies
View Related
