Trigger

CREATE TABLE tblA (
        Symbol               varchar(50) NOT NULL,
        APIFormat      varchar(50) NULL,
        DataFormat      varchar(50) NULL
 )

I would like to do the following:
If a new row is added or APIFormat is edited, and DataFormat is not being
updated, I
would like to set DataFormat like this:
If RIGHT(APIFORMAT,1) = '#', then SET DataFormat =
LEFT(APIFORMAT,LEN(APIFORMAT)-1) + '0'
If right(apiformat,1) <> '#', then SET DataFormat = APIFORMAT

If I do the below trigger, if I don't update DataFormat, it sets it
correctly, but if I do an update on DataFormat, it will be overriden by the
trigger.
For ex, if I do this:
UPDATE tblA SET APIFORMAT = '#.##',DATAFORMAT='#.###00' WHERE SYMBOL = 'FFA'
APIFORMAT will be #.##
and DATAFORMAT will be #.#0, instead of #.###00

Thanks

CREATE TRIGGER trgtblA
ON tblA FOR INSERT, UPDATE AS
IF @@rowcount = 0 RETURN;
IF TRIGGER_NESTLEVEL(object_ID(tblA)) > 1 RETURN;
SET NOCOUNT ON;

UPDATE tblA
SET DataFormat = CASE WHEN RIGHT(INSERTED.APIFormat,1) = '#'
THEN LEFT(INSERTED.APIFormat,LEN(INSERTED.APIFormat)-1) + '0'
ELSE INSERTED.APIFormat
END
FROM INSERTED INSERTED
JOIN tblAON INSERTED.Symbol = tblA.Symbol
RETURN;
GO





0
fniles
4/12/2010 8:38:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

1 Replies
766 Views

Similar Articles

[PageSpeed] 49

Inside a trigger, you can use the functions Update() and Columns_Updated() 
to determine which columns were affected; ie, which columns have been set 
explicitely in the Update or Insert statement.

Affected doesn't mean changed: if you update the value to the same value it 
was before, it will be marked as affected even if its value has remained the 
same.  I suppose that this is what you need.

-- 
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server 
(French)


"fniles" <fniles@pfmail.com> wrote in message 
news:%236qgGAo2KHA.3844@TK2MSFTNGP05.phx.gbl...
> CREATE TABLE tblA (
>        Symbol               varchar(50) NOT NULL,
>        APIFormat      varchar(50) NULL,
>        DataFormat      varchar(50) NULL
> )
>
> I would like to do the following:
> If a new row is added or APIFormat is edited, and DataFormat is not being
> updated, I
> would like to set DataFormat like this:
> If RIGHT(APIFORMAT,1) = '#', then SET DataFormat =
> LEFT(APIFORMAT,LEN(APIFORMAT)-1) + '0'
> If right(apiformat,1) <> '#', then SET DataFormat = APIFORMAT
>
> If I do the below trigger, if I don't update DataFormat, it sets it
> correctly, but if I do an update on DataFormat, it will be overriden by 
> the
> trigger.
> For ex, if I do this:
> UPDATE tblA SET APIFORMAT = '#.##',DATAFORMAT='#.###00' WHERE SYMBOL = 
> 'FFA'
> APIFORMAT will be #.##
> and DATAFORMAT will be #.#0, instead of #.###00
>
> Thanks
>
> CREATE TRIGGER trgtblA
> ON tblA FOR INSERT, UPDATE AS
> IF @@rowcount = 0 RETURN;
> IF TRIGGER_NESTLEVEL(object_ID(tblA)) > 1 RETURN;
> SET NOCOUNT ON;
>
> UPDATE tblA
> SET DataFormat = CASE WHEN RIGHT(INSERTED.APIFormat,1) = '#'
> THEN LEFT(INSERTED.APIFormat,LEN(INSERTED.APIFormat)-1) + '0'
> ELSE INSERTED.APIFormat
> END
> FROM INSERTED INSERTED
> JOIN tblAON INSERTED.Symbol = tblA.Symbol
> RETURN;
> GO
>
>
>
>
> 


0
Sylvain
4/13/2010 12:24:04 AM
Reply:

Similar Artilces:

Rob...Cost update trigger
I found a post that originally included a cost update trigger that Rob had created. It updates the sales price based on a new cost. I can't get the attachment anymore. Anybody out there have the trigger? Thanks, G E ...

Trigger
CREATE TABLE tblA ( Symbol varchar(50) NOT NULL, APIFormat varchar(50) NULL, DataFormat varchar(50) NULL ) I would like to do the following: If a new row is added or APIFormat is edited, and DataFormat is not being updated, I would like to set DataFormat like this: If RIGHT(APIFORMAT,1) = '#', then SET DataFormat = LEFT(APIFORMAT,LEN(APIFORMAT)-1) + '0' If right(apiformat,1) <> '#', then SET DataFormat = APIFORMAT If I do the below trigger, if I don't update DataFormat, it sets it correctly, but...

Trigger email
Is there a way that you can have a countdown and when the countdown reaches 0 for it to automaticly send out a email to a certian person? You will first have to determine how you are going to calculate your countdown. Then try the following: Dim strSubject As String Dim strAddress As String Dim strMessage As String Dim blSendEmail as Boolean strSubject = "Countdown reached zero" strMessage = "This is the message that will be the body of the email." If 'This is where you would put the countdown check value' Then ...

could not unregister BLM trigger
Hello: One of our Dynamics GP 9.0 clients is experiencing an unusual error message when exiting Report Writer and returning to GP. Although the error message is not priventing them from using Report Writer or GP, it is a point of interest. And, I would like to see what can be done to eliminate this message. The message is "Could not unregister the BLM trigger." Does anyone know what this means or how to eliminate it? I do not know, as of yet, if all workstations get this message or if even only just some users get this message. Thanks! childofthe1980s Hi I would sugg...

SQL Triggers in Headquarters
We are planning on implementing RMS and Headquarters for our multiple-store retail sales (do not have it installed yet). We are also going to have a virtual store, which will sell items from our brick-and-mortar store inventory. Without getting into too many details at this time, we have determined that the best way for us to accomplish our business needs is to have an external SQL database which will be used to keep track of brick-and-mortar store inventory and will also be used to record virtual store sales. We would then have triggers on this database and on the Headquarters databas...

Possible to trigger rules from MSN through VBA?
I understand that it is not possible to apply rules to email that comes in from MSN. In my case, my Outlook 2003 knows about two email sources, a POP3 account and an MSN account. Is it possible to write a VB macro that, upon delivery of an MSN email message, transfers that email message to the top of the POP3 email tree and have the rules execute on it from there? -- Richard Lewis Haggard www.Haggard-And-Associates.com ...

Trigger on IVR10015
I'm trying to find a way to capture the actual created date on new items when the item copy function is used (when you use the copy function it stores the created date of the source item as the created date of the new item). We have manufacturing but we don't have the QA module, so I was trying to add a trigger to IVR10015 to put the current date in the QA Start Date field and or the QA End Date field upon insert to IVR10015. When I do that and add a new item I get the message below. How can I tell what the constraint is that is being violated? Any other ideas on how to capt...

Replication Not being Triggered
Guys, I've created a new user more than 15 minutes ago and this user has not been replicated yet from the branch to my hub site. I'm testing the replication because of a few complaints of late that the replication is not happening as fast as it should. Any ideas of where I can start looking. I tested using dcdiag, repadmin and everything is ok. However, if i force the replication repadmin /replicate it works fine. I don't want to have to invoke replication everytime an object is created. Nik. Nik schrieb: > I've created a new user more than 15 minutes...

Can't get trigger to work with linked SQL Instance...
I'm trying to create a trigger on the Customer Master Table (RM00101) that will insert certain fields from a new record into another table on a seprate instance of SQL. I have linked the 2nd instance to the first from within Enterprise Manager. Whenever a new Customer record is created and saved the following SQL exception is thrown : "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query" I have checked several sources for the cause...

About trigger
Hi. My application works on Accessa 2000. Alla my data are in SQL server, so there is an ODBC link between them. I have a table which includes for instance names of countries. Is it possible to creater some kind of trigger or something else to automatically popup a message box to each user, when someone adds a new coutnry to the database? User would not have to go and see, whether there are new countries in database, but the application tells the user when new countries are added. thanks Hannu ...

Triggers
Greetings... I'm getting more and more sophsiticated -- but its getting scarier and scarier... I'd like to have a macro run when a user changes the value in a certain cell (via a dropdown picklist). The macro hides certain lines depending upon the view chosen. How can I do this? I know marcro's need to be RUN explicitly -- yet there seems to be some sort of EVENT command. Many thanks.../Randy Randy, There are plenty of events that happen in Excel. Go to the sheet module for your sheet (double-click it in the Project Explorer to open the code window). Open the Object dropdo...

Debug Trigger
Hi Folks Trying to develop a trigger - what is the best way to debug a trigger? Any log files or query analyzer available to assist in debugging. RMS version 1.3R MSDE Database Thanks Dan Danny wrote: > Hi Folks > > Trying to develop a trigger - what is the best way to debug a trigger? Any > log files or query analyzer available to assist in debugging. I not know a tool specific to debug the trigger.. I test the code through the query analyzer and sql profiler ... cod hi Danny, the excellent tool is sql profile tells you what happen. I traced many issue and resolve u...

trigger
I want to add a trigger to one of the CRM tables to perform an action on a different database. It's on an activity so I can't just use the Workflow Manager. I am not going to change any of the CRM data with this trigger. Can anybody tell me why Microsoft would invalidate the support agreement because of this? I understand if I went ahead and changed data, but I'm just adding a trigger that is not touching the CRM data. Thanks. I think you can safely add that trigger, MS will not support on any upgrades, patches, hotfixes, or errors resuling in addition of trigger. Other tha...

SQL Trigger when Tendering
I would like to add a comment to each TransactionEntry record, which would show up on the receipt. Rather than write a DLL, I thought a SQL Trigger would be simple enough. (Yes, I know my way around SQL. :-) The trigger, itself, seems to work fine. Unfortunately, the comment doesn't show up on the receipt. It does when I add a comment directly at POS, so I know the receipt definition can handle a comment. And after Tendering, the correct value appears in the Comment field of the TransactionEntry table. But it's like the receipt (journal?) image is getting generated before the Tr...

Click ok to MessageBox in vb.net triggers form.activate in calling form
Hey guys, We're in the process of converting a vb6 app to vb.net 2008 and are having some minor issues. One of these is that it appears that the form.activate code is fired after clicking ok to any messageboxes that we display. Is this normal? Is there any way to call a messagebox in a way that won't fire the form.activate when clicking ok? We have much of our form setup code in there which we don't want run again. Sure we can code around this with flags but I thought maybe there's a way to call the msgbox with an optional param or similar? Thanks in A...

trigger on select
Hi all, I know that "trigger on select" does not exists, I'm searching something to accomplish this need: I have a table, let's say: [Some_table] [ID] int primary key [Flag] bit [Some_Field] nvarchar(40) when a user (all user) does a select on a row of that table that has [Flag]=1 the select statement does not returns the correct value for [Some_Field] column but a specific value that I specify in the "trigger on selest". How can I do this? "Andrea Caldarone" <andrea.caldarone@poste.it> wrote in message news:...

Activity workflow - assign - event triggered when follow-up task created
Hi, I have created a workflow rule under Task > Assign with an action to send an e-mail to the owner stating a task has been assigned to them. The rule works great to notify users when tasks are assigned to them, however, it seems to be additionally triggered when a user Creates a new task using the "Follow-up" (Form Assistant). In this case it causes an e-mail to go out to the individual creating the task, which is more of a nuissance than anything. Has anyone experienced this or have an alternative? Thank you, Graeme Hi, before sending an email, You could check i...

Triggers
When I launch GP V9.0, I get a message that says "Error Registering Triggers - Contact your System Administrator. This happens in all of the companies. I have searched all of the possible resources to identify a resolution of this error. Does anyone have a suggestion that I can follow to fix this error? A quick and detailed response will be most appreciated. Thanks to all in advance. -- Deesom I am assuming that you have recently upgraded to v9.00. I believe your issue is caused by having Dexterity customisations from a previous version on your system. Please look at your DYN...

Cell entry triggers Worksheet_SelectionChange event
Tested in Excel 2003 fr, Excel 2007 fr, Excel 2010 beta en. When you enter any character in a cell, the Worksheet_SelectionChange event macro is fired (the target cell is still the active cell, no move after entry). Anyone heard of it ? TIA Daniel code NOT restricted? As ALWAYS, post your code for comments. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Daniel.C" <dcolardelleZZZ@gmail.com> wrote in message news:u4fO5UraKHA.2680@TK2MSFTNGP04.phx.gbl... > Tested in Excel 2003 fr, Excel 2007 fr, Excel 2010 beta en. &...

Using ODBC in a trigger
Can someone point me in the right direction on how to connect 2 tables, in 2 different SQL Servers, with ODBC? I have no idea what the syntax would be for the connect. ON INSERT into Table1, I need to INSERT that record into Table2 on the remote SQL Server. thanks! On Aug 7, 2:54=A0pm, "shank" <sh...@tampabay.rr.com> wrote: > Can someone point me in the right direction on how to connect 2 tables, i= n 2 > different SQL Servers, with ODBC? I have no idea what the syntax would be > for the connect. > > ON INSERT into Table1, I need to INSER...

Runing two macros triggered by a button
I have the first macro set-up to start when a button is clicked, I would like bothe the macros to run when then button is clicked, any help would be great, not real up on this macro stuff yet. MACRO 1 (triggered by button) Sub nametab() Dim i As Integer On Error Resume Next For i = 1 To Sheets.Count Sheets(i).Name = Sheets(i).Range("G1").Value Next i On Error GoTo 0 End Sub WANT TO ADD THE FOLLOWING TO TRIGGER AT THE SAME TIME Sub CellInfooter() With ActiveSheet .PageSetup.CenterFooter = .Range("G1").Text End With End Su...

Insert trigger not appearing to work for bulk insert with FIRE-TRIGGERS
I have an insert trigger that works just fine when I insert one row When I perfrom a bulk insert, the trigger only seems to work on the last row in the input file. What should the trigger be doing to process each row of the inserted data? Cheers Bob What's your trigger code look like? -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ---------------- "thing" <someone@microsoft.com> wrote in message news:O4c4cSGeKHA.3792@TK2MSFTNGP02.phx.gbl... >I ha...

Triggering system tray app from custom button
Is there a way to trigger a program that's running in the system tray from a custom button? Tom -- The worst words in business: "We''ve always done it that way" -- Stop Fishing for eMail. ...

Triggers after Insert
Hi, I'm having a temporary table in SQL Server 2005, which receives the data from external Orace DB. The data from Oracle DB inserts the data once in a month time. How to create a trigger which calls the storedprocedures, after all the data is get inserted to the temporary table, not for each row insertion. On Sat, 12 Jun 2010 04:01:21 GMT, "Visha" <u60840@uwe> wrote: >Hi, > I'm having a temporary table in SQL Server 2005, which receives the data >from external Orace DB. The data from Oracle DB inserts the data once in a >month time. >...

Help with Trigger
I would really appreciate some help with a trigger I have a Soheader table and a customer table. I would like upon data entry to update the Soheader.sotypeid field with the customer.user5 field when the soheader.custid is selected. so upon entry or change of the soheader.custid I would like the Soheader.sotypeid field to get updated with the value on the customer.user5 field. where soheader.custid = customer.custid thanks very much S Commar Sammy (s_commar@hotmail.com) writes: > I would really appreciate some help with a trigger > > I have a S...