How To Write Trigger Based On Certain Condition
One of my table called as 'customertable' contains following fields
customername, emailid, subscriptionendperiod
Example records are:
david, david@john.com, 12/20/2005(mm/dd/yyyy format).
My question is that: Just one month before subscriptionendperiod that is on 11/20/2005(mm/dd/yyyy) an automatic email should go to david@john.com with the message 'Your subscription period ends on 12/20/2005'
How to write trigger for this.
Please Note : No ASP code is invloved in this.
Only MSSQL coding to be done.
Regards
View Complete Forum Thread with Replies
Related Forum Messages:
How To Write This Condition?
Hi, I am trying to set up a conditional split. As usual, my books do not explain what I am trying to do :-) flat file source --> conditional split (if Column0 contains a certain string send to error file) In the Conditional Split Transformation Editor I have the following for "condition": FINDSTRING( "my search string", [Column0] ,1 ) TITLE: Microsoft Visual Studio ------------------------------ Error at myPkg [Conditional Split [8259]]: The expression "FINDSTRING( "my search string[Column0] ,1 )" is not Boolean. The result type of the expression must be Boolean. Error at myPkg[Conditional Split [8259]]: The expression "FINDSTRING( "my search string[Column0] ,1 )" on "output "Case 1" (8351)" is not valid. Error at myPkg [Conditional Split [8259]]: Failed to set property "Expression" on "output "Case 1" (8351)". As you can see I want it to search Column0. Can someone please point out my error. Thanks
View Replies !
How To Put Condition In Select Statement To Write A Cursor
col1 col2 col3 col4 36930.60 145 N . 00 17618.43 190 N . 00 6259.20 115 N .00 8175.45 19 N .00 18022.54 212 N .00 111.07 212 B .00 13393.05 67 N .00 In above 4 col if col3 value is B then cursor has to fectch appropriate value from col4. if col3 value is N then cursor has to fectch appropriate value from col1. here col2 values are unique. Can any one reply for this..............
View Replies !
How To Write Condition In The Conditional Split Control In SSIS
HI i need to write the Condition for Insert and Update Reccord depending upon the Prod_ID. How to write the Follwing condtion in the Condition Split? pls Anyone give me the Solution? " if Prod_ID Exist then UPDATE Records if Prod_ID Not EXIST then INSERT Records " how to write the above conditon in the Condional Split? Thanks & Regards, Jeyakumar.M chennai
View Replies !
Incrementing Based On Condition
Hi, I am facing a challenge and hope some one can help me with the query. I have a school. School have classrooms. Classrooms are divided into various sections (Section A, Section B and so on) . Sections have subsections. Every student is allocated a rollnumber in that section.(Subsection is just for dividng the sections. it has no other use.) Now the student is given a choice to specify his own roll( DesiredRoll) in that section. If two children select the same rollno, then the system internally allocates a trackingno.(There can be multiple roll no as these are allocated manually by admin). Let me demonstrate this: So when the first entry is made in the db, and let us say that the section to be allocated is A, RollNo is 1 and the DesiredRoll is 1 Student 1 Section A Subsection 1 Roll No 1 DesiredRoll 1 TrackingNo 0 The second entry is made in the db and let us say the section to be allocated is A, RollNo is 2 and the DesiredRoll is 2 Student 2 Section A Subsection 1 Roll No 2 DesiredRoll 2 TrackingNo 0 Now let us say there is a 3rd entry the section to be allocated is A, RollNo is 3 but the DesiredRoll is 1. Now since the DesiredRoll has already been taken, we will allocate the DesiredRoll 1, however now the trackingNo will be 1 Let us say there is a 4th entry is made, the section to be allocated is A, RollNo is 4 but the DesiredRoll is again 1. Now since the DesiredRoll has already been taken, we will allocate the DesiredRoll 1, however now the trackingNo will be 2 Similarly this logic will work for different sections. How will I write a query so that I can detect this scenario and increment the tracking no or allocate a tracking no of 0 if there is a new entry made in that section The structure of the table is as follows: IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Student]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Student]( [RID] [int] NOT NULL, [Class] [int] NULL, [Section] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [SubSection] [int] NULL, [RollNo] [int] NULL, [DesiredRoll] [int] NULL, [TrackingNo] [int] NULL ) END GO INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (1, 1, N'A', 1, 1, 1, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (2, 1, N'A', 1, 2, 2, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (3, 1, N'A', 1, 3, 1, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (4, 1, N'A', 1, 4, 1, 2) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (5, 1, N'A', 12, 3, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (6, 1, N'A', 12, 4, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (7, 1, N'B', 5, 1, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (8, 1, N'B', 5, 2, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (9, 1, N'B', 5, 3, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (10, 1, N'B', 10, 1, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (11, 1, N'B', 10, 2, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (12, 1, N'B', 10, 3, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (13, 1, N'B', 11, 1, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (14, 1, N'B', 11, 2, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (15, 1, N'B', 11, 3, 0, 0) Thanks
View Replies !
JOIN Based On LIKE Condition
I'm trying to join two tables based on a like condition. The first table contains the full IP, e.g. '166.27.12.24' and the second contains a 2 octet range, e.g. '166.27', which I need to join. Table 1 -> TRAFFIC (Time, SourceIP) Table 2 -> IP_ROSTER (IP2OctetRange, Administrator) I've tried the following, but it does not seem to work: SELECT TOP 100 SOURCE_IP, r.IP2OctetRange, r.Administrator FROM TRAFFIC LEFT JOIN IP_ROSTER AS r ON SOURCE_IP LIKE RTRIM(LTRIM(IP2OctetRange))+'%'
View Replies !
Returns TABLE Based On A Condition
HI all,In SQL Server, i have a function which will return a table. likecreate function fn_test (@t int) returns table asreturn (select * from table)now i want the function to retun the table based on some condition like belowcreate function fn_test(@t int) returns table asif @t = 1 return (select * from table1)else return (select * from table2)It is not working for me. Please give me your suggesstions. It's very urgent.Thank you in advance....
View Replies !
Condition Joins Based On Parameters
I'm using SQL Server 2005 Reporting Services. Apologies if this is in the wrong area. I'm not sure if it's possible but what I want to do is join table dimFactor to factMedia using different fields dependent on the partameter value. i.e from FactMedia fm... if parameter value = 1 INNER JOIN dimFactor df ON df.FactorID = fm.AgeGroup_ID if parameter value = 2 INNER JOIN dimFactor df ON df.FactorID = fm.Gender_ID and so on.. Can this be done and if so, how?? I've tried using CASE statements but it's not having any of it. Help please!
View Replies !
Reordering Rows Based On A Condition
Hi, I have two tables : Students and StuHistory. The structure of the Student table is as follows : IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Student]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Student]( [RID] [int] NOT NULL, [Class] [int] NULL, [Section] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [SubSection] [int] NULL, [RollNo] [int] NULL, [DesiredRoll] [int] NULL, [TrackingNo] [int] NULL, [Original_rollno] [int] NULL, [StudentStatus] [int] NULL ) END GO A section has subsections where students are allocated rollno's. Every student has a unique roll no in that subsection. However he is also given a choice to enter his desired roll no. If more than one student choose the same desired roll no in that subsection/section, there is a [TrackingNo] field that then starts keeping a count. For the first unique desired roll no in that subsection/section the tracking no is always 0. [StudentStatus] represents the following : (-1 for deleted, 0 for edited, 1 for newly inserted). After every fortnight, i have to run a batchquery that does the following: 1. all students marked with -1 are moved to a table called StuHistory which has the same structure as that of Student. 2. Now oncethe -1 status students are moved, there will be a gap in the roll no. I want to reallocate the rollnos now, where rollnos = desired roll no taking into consideration the trackingno So if 4 students have chosen the desired roll no as 5 and their current roll no is scattered in a subsection lets say 7, 10, 14,16, then while rearranging they will be together(grouped by subsection/section) and will be allocated roll no's 5,6,7,8. The other students will be moved down based on their desired roll nos. Over here i have to also fill the gaps caused because of the students who were deleted. How do i write query for this? I have been struggling. I thought of posting this as a new post as it was mixed in the previous post. Script : INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (1, 1, N'A', 1, 1, 1, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (2, 1, N'A', 1, 2, 2, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (3, 1, N'A', 1, 3, 1, 1,0,1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (4, 1, N'A', 12, 1, 1, 0,-1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (5, 1, N'A', 12, 2, 1, 1, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (6, 1, N'A', 12, 3, 2, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (7, 1, N'B', 5, 1, 3, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (8, 1, N'B', 5, 2, 3, 1, 0 ,1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (9, 1, N'B', 5, 3, 3, 2, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (10, 1, N'B', 5, 4, 2, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (11, 1, N'B', 5, 5, 2, 1, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (12, 1, N'B', 10, 1, 1, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (13, 1, N'B', 10, 2, 1, 1, 0, 1 ) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (14, 1, N'B', 10, 3, 1, 2, 0, -1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (15, 1, N'B', 10, 4, 2, 0, 0, 1) Thanks.
View Replies !
Insert Into Temp Table Based On If Condition
hello all,this might be simple:I populate a temp table based on a condition from another table:select @condition = condition from table1 where id=1 [this will giveme either 0 or 1]in my stored procedure I want to do this:if @condition = 0beginselect * into #tmp_tablefrom products pinner joinsales s on p.p_data = s.p_dataendelsebeginselect * into #tmp_tablefrom products pleft joinsales s on p.p_data = s.p_dataendTha above query would not work since SQL thinks I am trying to use thesame temp table twice.As you can see the major thing that gets effected with the condictionbeing 0/1 is the join (inner or outer). The actual SQL is much biggerwith other joins but the only thing changing in the 2 sql's is the joinbetween products and sales tables.any ideas gurus on how to use different sql's into temp table based onthe condition?thanksadi
View Replies !
Check Certain Row Of Record Based On Special Condition
let say i have such data in table ------------------------------- Number | Descriptions 5 | xxxxxxx 4 | xxxxxxx 3 | xxxxxxx 2 | xxxxxxx 1 | xxxxxxx 0 | xxxxxxx <- 5 | xxxxxxx <- 4 | xxxxxxx anyway to select 0 | xxxxxxx 5 | xxxxxxx out? based on the number of next row is 5, and current number is 0. because i need to description data.
View Replies !
Showing An Image Based On A Boolean Condition
I have a table object attached to a dataset, One of the columns is based on a boolean value(bit field) from the database. I have a check picture and an unchecked picture. How can I show the selected image based on the result? links or ideas welcome
View Replies !
How To Make Table Row Invisible Based On Certain Condition
HI I have the following scenario in my report. -The data is displayed in a table -The table groups by one field -Each table row calls a subreport -There is about 6 paramaters in the report -The last paramater of the list of paramters is a multivalue paramater and based on what is selected in the list the corresponding subreport must be shown. -So i use a custom vbscript funtion to determine if a specific value was selected or not. This functionality is working fine. My problem is if the user does not select all the values in the multi select then i want to make the row invisble and remove the whitespace so that there is not a gap between the other subreports which is shown. I can make the subreport invisible inside the row but there is still the white space which does not display very nicly. How can i make the row invisible if the vbscript function that is called returns a false value? Here is the funtion I call -> Code.InArray("ValueToSearchFor", Parameters!MultiValueDropDown.Value) The Function returns a true or false. Thanks.
View Replies !
Getting Column Names And Its Values Based On Condition
Hi, I have a table as follows Table Master { Id varchar(20), Cat1 datetime, Cat2 datetime, Cat3 datetime, Cat4 datetime } and the data in the table is as follows Table Master { Id cat1 cat2 cat3 cat4 ----------------------------------------------- 1 d11 null d13 d14 2 d21 d22 d23 d24 3 NULL d32 d33 d34 4 d41 d42 NULL NULL } I want to retrive column names and its values wheb the ID matches to some value. Can any one please let me know how to do this? Thanks alot ~Mohan
View Replies !
How To Generate Condition Based Aggregate/summary Data
Hi I have a source table with App_Id, App_Type , Tier_Type, Date_Id as columns. I have a destination table with Date_Id, Total_App_Count, Booked_App_Type_Count, Approved_App_Type_Count columns. I need to count the Total number of records , count of records with App_Type = "Booked" and count of records with App_Type = "Approved" and populate in my destination table. What all the steps i need to create in SSIS to get this results ? Thanks Kumaran
View Replies !
How To Make Subreport Visible Based On Parameter Condition?
Hi, I have a subreport added to the main report and I want to make this report visible only when the parameter value is met. Ex, I have a parameter CustName in the main report and want to show the subreport when the custName = xxxxx. There reports are parameter driven not data driven reports. Any help is greatly appreciated. Thanks, Sirisha
View Replies !
Adding A Column Name To A Table In Each Of The Databases Based On A Condition
i have the folowing databases DB1,DB2,DB3,D4,DB5........ i have to loop through each of the databases and find out if the database has a table with the name 'Documents'( like 'tbdocuments' or 'tbemplyeedocuments' and so on......) If the tablename having the word 'Documents' is found in that database i have to add a column named 'IsValid varchar(100)' against that table in that database and there can be more than 1 'Documents' table in a database. can someone show me the script to do it? Thanks.
View Replies !
Inserting Values Intoto Only Column Based On A Condition
Hi, I have at table as foolows Table Cat3 { ID, Update datetime } and also have a master table as follows Table Master { ID, Cat1 Datetime, Cat2 datettime } My requirement is to alter themaster table schema i.e to add a column with the name as of the table name i.e Cat3 and will lok lie as foolows Table Master { ID, Cat1 Datetime, Cat2 datettime, Cat3 Datetime } I would like to insert the data to this column. The sample output is as follows Before insertng the data of table ;Cat3' into the Master table Table Master { Id Cat1 Cat2 --------------------------------- 1 D1 D2 2 D2 NULL 3 D3 D4 } And The Cat3 table data is as follows Table Cat3 { ID Update -------------------------- 1 D5 3 D6 4 D7 } The final putput of the master table should be as follows Table Master { Id Cat1 Cat2 Cat3 ------------------------------------------------------ 1 D1 D2 D5 2 D2 NULL NULL 3 D3 D4 D6 4 NULL NULL D7 } Can any one please let me know the query to achieve this Thank you very much for your time and support ~Moahn
View Replies !
Delete Multiple Rows One At A Time Based On A Condition
Hi, I have the following scenario : CustomerDetail customerid customername status app_no [status = 0 means customer virtually deleted] CustomerArchive archiveno [autoincrement] customerid customername status At the end of the month, I have to physically delete customers. I have written two stored procs: proc1 create proc spoc_startdeletion as declare @app_no int select @app_no = (select app_no from customerdetail where status=0) EXEC spoc_insertcustomerarchive @app_no -- After transferrin, physically delete delete from customerdetail where status=0 proc2 create proc spoc_insertcustomerarchive @app_no int as insert into customerarchive(customerid,customername,status) select customerid,customername,status from customerdetail where app_no = @app_no It works fine if there is only one row with status=0, however the problem is that when there are multiple rows in customerdetail with status=0, it returns 'Subquery returned more than one value' How can i transfer multiple rows one by one from the customerdetail to customerarchive and then delete the rows once they are transferred. Vidkshi
View Replies !
Trying To Create A Proc That Will Insert Values Based On A Condition That Is Another Table
Can someone give me a clue on this. I'm trying to insert values based off of values in another table. I'm comparing wether two id's (non keys in the db) are the same in two fields (that is the where statement. Based on that I'm inserting into the Results table in the PledgeLastYr collumn a 'Y' (thats what I want to do -- to indicate that they have pledged over the last year). Two questions 1. As this is set up right now I'm getting NULL values inserted into the PledgeLastYr collumn. I'm sure this is a stupid syntax problem that i'm overlooking but if someone can give me a hint that would be great. 2. How would I go about writing an If / Else statement in T-SQL so that I can have the Insert statement for both the Yes they have pledged and No they have not pledged all in one stored proc. I'm not to familar with the syntax of writing conditional statements within T-SQL as of yet, and if someone can give me some hints on how to do that it would be greatly appriciated. Thanks in advance, bellow is the code that I have so far: RB Select Results.custID, Results.PledgeLastYr From Results, PledgeInLastYear Where Results.custID = PledgeInLastYear.constIDPledgeInLastYear Insert Into Results(PledgeLastYr) Values ('Y')
View Replies !
HELP With SQL Query: Select Multiple Values From One Column Based On &&<= Condition.
Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation: I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner: (AISC_Shapes_Table) AISC_MANUAL_LABEL W W44x300 300 W42x200 200 (and so on) WT22x150 150 WT21x100 100 (and so on) MT12.5x12.4 12.4 MT12x10 10 (etc.) I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is: Code SnippetSELECT AISC_MANUAL_LABEL, W FROM AISC_Shape_Table WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%')) It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance. Regards, Steve G.
View Replies !
CLR-Based Trigger? Recursive Trigger? Common Table Expression?
Hey, I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction. A quick explanation of the various tables I'm dealing with: WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc. Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below) Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government] Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc. Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies. Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents. A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table. Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed. If you need the design or create script (table layout), please let me know. Thanks.
View Replies !
Please Help Create Trigger Condition On Update
please help create trigger condition on update IF EXISTS ( SELECT * FROM empList WHERE (unit = 9) ) begin update [dbo].[empList] set unit = CASE WHEN SELECT na,empID, unit FROM empList WHERE (empID IN (111, 222, 333, 555)) AND (unit = 9)) then '4' else t.fld1 end i have an emmployee table empid unit ------------------------------------------------------ 1111 3 2222 9 3333 9 4444 2 5555 2 6666 1 7777 9 8888 2 9999 9 ----------------------------- i need help create trigger condition on update like this ONLY ON EMPID=2222,3333,7777,9999 WHAN ON UPDATE they have unit =9 THAN I NEED THE trigger DO THIS empid unit ------------------------------------------------------ 1111 3 2222 4 3333 4 4444 2 5555 2 6666 1 7777 4 8888 2 9999 4 ONLY IF someone update EMPID=2222 OR 3333 OR 7777 OR 9999 and UNIT=9 THAN automatic change 9 TO 4 TNX for the help
View Replies !
How To Write Set-based SQL Instead Of Cursor
Guys Here's the scenario create table data1 (dealid varchar(6) , datex smalldatetime , Tn INT) insert data1 (dealid , datex , Tn ) values ('12345' , '31-12-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('12345' , '30-11-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('12345' , '31-10-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('98765' , '31-12-2005' , 2) insert data1 (dealid , datex , Tn ) values ('98765' , '30-11-2005' , 1) insert data1 (dealid , datex , Tn ) values ('98765' , '30-11-2005' , 0) select * from data1 I need to update the Tn column from the default 9999 for the 3 rows in this table where the dealid is 12345 based on the value in the datex column so the row with the 'highest ie most recent date' gets a 0. I then need to assign the value 1 to the next highest and so on until all rows (in this case 3) get incrementing integer values. It's easy with a cursor but can't get my head round doing it in a set-based way Any ideas
View Replies !
Select Criteria Based On Date - Cleaner Way To Write?
Code: SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS + AUG_CURR_CREDITS + AUG_CURR_DEBITS + SEP_CURR_CREDITS + SEP_CURR_DEBITS + OCT_CURR_CREDITS + OCT_CURR_DEBITS + NOV_CURR_CREDITS + NOV_CURR_DEBITS + DEC_CURR_CREDITS + DEC_CURR_DEBITS + JAN_CURR_CREDITS + JAN_CURR_DEBITS + FEB_CURR_CREDITS + FEB_CURR_DEBITS + MAR_CURR_CREDITS + MAR_CURR_DEBITS + APR_CURR_CREDITS + APR_CURR_DEBITS + MAY_CURR_CREDITS + MAY_CURR_DEBITS + JUN_CURR_CREDITS + JUN_CURR_DEBITS) as CURR_AMT I need to sum these amounts running from July to the month prior to whatever the current month is. So if it was August, it would only be Code: SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS) as CURR_AMT Is there a cleaner (shorter) way to iterate through the twelve months than either writing the query 12 times in an IF statement, or 12 CASE statements? This is only part of a query that joins several tables (not shown). Any suggestions on the best way to write this would be valued.
View Replies !
Write A Stored Procedure Based On Recursive Data.
Hello, I am hoping someone can help me in this. I am looking to write a stored procedure that will return the heirarchy of an organization. I will display how the heirarchy might look and then list the tables involved. John Smith - Jacob Jones - Lisa Thompson - Samuel Barber - Paul Smith - John Jackson Ok, so Jacob, Lisa, an Samuel report up to John Smith. Paul and John Jackson report up to Samuel Barber. Here are the tables: Users holds the user_id, first_name, last_name, and reports_to_user_id. User_Roles holds the user_id, role_type_id Role_Types holds the role_type_id, and the type (which could be Administrator, Standard, Guest) for example. In addition, Role_Types also has ranking which must be taken into consideration as well. 1 being the top rank and 9 being the lowest. Thanks very much in advance, Saied
View Replies !
How To Write This Trigger
I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I also have T2 in Database2 which has the same fields and some extra fields. Now based on the T1.Type=Insert or Update, I need to perform either update or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>’Done’. If this is a success, I need to set T1.Status=’Done’ for the updated records. So this should only be updated records. T1 is a frequently inserted table, so there might be more than one record coming there at the same time. How should I write my insert trigger and correctly set T1.Status=’Done’, any example would be greatly appreciated.
View Replies !
How To Write A Trigger
I have a table with a last_updated field and a review_date field. I would like to write a trigger that when the last_updated field is updated, it adds 6 months to the review_date field. This is what I started, not sure of how to finish it: CREATE TRIGGER Update_Review_Date ON dbo.Category_Type FOR Update AS
View Replies !
Where Do We Write A Trigger
Hi, I am pretty new to MSSQL server environment. In Oracle i write Trigger and compile it at command prompt in case of SQL plus the same way i need to know where we write the script for SQL server. Is there any menu driven editor where we can write or simply writing a trigger in SQL Query analyzer and executing it will work Please advice. Thanks and Regards D. Pavan Kumar
View Replies !
Write A Trigger
Hi all, i want to write a trigger which returns a row to the application whenever a row is inserted into a table. E.g. When row R1 is inserted into a table T1. the trigger should return the R1 to the application. Wanna be coding monkey...
View Replies !
How To Write This Trigger
I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I also have T2 in Database2 which has the same fields and some extra fields. Now based on the T1.Type=Insert or Update, I need to perform either update or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>€™Done€™. If this is a success, I need to set T1.Status=€™Done€™ for the updated records. So this should only be updated records. T1 is a frequently inserted table, so there might be more than one record coming there at the same time. How should I write my insert trigger and correctly set T1.Status=€™Done€™, any example would be greatly appreciated.
View Replies !
How To Write Trigger
hi I want to audit data before update or delete with modified date,userid,machineid How to write trigger for it Example ------- I have a table as Immigration With Following fields Employee_id int(foriegn key) Passport no varchar(50) Issue date datetime Expiry date datetime Iwant to store data or audit data before update or delete data from above table Malathi Rao
View Replies !
How To Write Trigger For ON_DELETE_CASCADE?
Hello ExpertsI am new in Sql triggers and i would like some helpI have a Table called ITEM and another callded SOURCE[ITEM]ItemNo,ItemName,SourceNo[Source]SourceNo,Sourcei want to create a Trigger so when i delete a record from [SOURCE] allthe records in [ITEM] where item.SourceNo = [SourceNo that I deletedfrom SOURCE]How can i do that?
View Replies !
CLR Trigger To Write File.......
Hello, theres, I have a request to write to a file whenever new record added to the table. When records insert row by row, it goes well. but when more than 2 session insert at the same time, sometimes it duplicate some record in the file. I try to add synchonize code ( like lock , Monitor) but it doesn't work. any idea ? Regards, Agi
View Replies !
Trigger To Encrypt Field Before Write
Hello,While working through my encryption questions from preivous posts, I amfinding that I may have to resort to use triggers to do the encryption(not that this is the only way, but might be the best way for mycircumstances).I would like to create a trigger that will encrypt the field before thewrite is committed.I've found serveral posts about triggers, but nothing that fits what Iwish to do.Upon an insert or update I want to modify the ssn field with this:cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), cast(SSN asnvarchar(11))) as nvarchar(40))so, ssn '123456789' in SSN would become <something encrypted> in SSNThis is the trigger I have so far, but it is generating an error:CREATE TRIGGER F_Student_SSN.eSSNON F_STUDENT_SSNINSERT, UPDATEAS SELECT cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), cast(SSN asnvarchar(11))) as nvarchar(40))TIARob
View Replies !
Trigger To Write From Sql To Oracle Table
I am trying to insert into oracle table as soon as data is inserted in sql server table from an 3rd party application. If I execute stored procedure to insert or if I insert using regular insert statement from sql query analyzer it inserts data in oracle...but if I try using a trigger it says...MSDAORA doesnot support...distributed transactions... Please advise if you have any suggestions
View Replies !
How To Write A Trigger Restict Delete Or Update
hi All I have an administrator account in my users table, I want to restrict user delete or update (modify) this account, how can I write a trigger to do that ? Here is missing my statement : CREATE TRIGGER UnModify_Admin_Account ON Users AFTER DELETE,UPDATE AS BEGIN SET NOCOUNT ON; // if username is 'Administrator' RAISERROR and return @@ERROR END GO Many Thanks in advance ! Regards, QuachNguyen
View Replies !
Need A Trigger Based On A Row
Hello, everyone I need to create a trigger to update a FLAG column by row modification. The table likes, Col1FLAG a0 b0 c0 d0 e0 If "a" in Col1 is changed, the "0" in same row (First row) should be changed to '1'. Other FLAG values should not be changed. The same rule for other row. Anyhelp will be approciated. Thanks a lot ZYT
View Replies !
Trigger Based On Specific Field??
Do triggers work on the field level or only for full table updates, etc...? I want to have a trigger that will change a field on another table based on the update of a specific field on a table. Could anyone provide a sample please, if this is possible? Thank you!
View Replies !
Insert / Update Trigger Based On A Column's Value
I am new to triggers and surely could use some help. I can create a trigger to insert related records based on the main tables ID and insert that value into other related tables fine... but... How do I create a trigger that can insert a record into one table for a columns given value and then insert a record into another table for another given value? For instance: New row... Table1, Column1 (PK) has a value of 101 Table1, Column2 has a value of 'Blue'. // When a new row is created in Table1 and Column2 has a value of 'Blue'... I want to insert a new row into Table2 - with Table1 Column1's value. // Now if Table1, Column2 has a value of 'Red' when the new row was created... I want to insert a new row into Table3 - with Table1 Column1's value. Not Table2 This has to be inserted into one or the other tables based on column2's value, not both. Then I want to populate the other related tables (Table4, Table5) with the regular insert statements based on Table1 Column1's value. This (the conditional part above) has to work with an update to Table1 also. So if someone came back to that record and changed Column2's value from 'Blue' to 'Red', it would have to delete the appropriate record in Table2 and then insert the new row into Table3 and visa-versa. Can I do this with one trigger? Thanks
View Replies !
Need Input On Trigger Based Database Auditing
Hi I am building a pretty simple intranet application where we need to be able to track changes to tables. The tracking feature do not need to be very advanced, we just need to see who changed something and what it was. Therefore I decided just to use a trigger based solution, but need some input/advice since my SQL skills is somewhat lacking. Consider the following (mock-up) schema: -- My content table CREATE TABLE [Content]( [ContentGuid] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newid()), [Data] [nvarchar](4000) NOT NULL, [ChangedBy] [nchar](10) NOT NULL, [MaybeNull] [int] NULL ) -- My history table CREATE TABLE [History]( [ChangedTable] [nvarchar](50) NOT NULL, [ReferenceGuid] [uniqueidentifier] NOT NULL, [ChangedBy] [nchar](10) NOT NULL, [ChangedOn] [datetime] NOT NULL DEFAULT (getutcdate()), [IsDelete] [bit] NOT NULL DEFAULT ((0)), [Changes] [xml] NOT NULL ) ON [PRIMARY] -- My insert/update trigger CREATE TRIGGER [RecordChangeOnInsertUpdate] ON [Content] AFTER INSERT,UPDATE AS BEGIN SET NOCOUNT ON; DECLARE @Changes xml SET @Changes = (select * from [inserted] for xml raw ('Content'), elements xsinil) INSERT INTO [History] ([ChangedTable] ,[ReferenceGuid] ,[ChangedBy] ,[ChangedOn] ,[IsDelete] ,[Changes]) SELECT 'Content', ContentGuid, ChangedBy, getutcdate(), 0, @Changes FROM [inserted] END -- My delete trigger CREATE TRIGGER [RecordChangeOnDelete] ON [Content] AFTER DELETE AS BEGIN SET NOCOUNT ON; DECLARE @Changes xml SET @Changes = (select * from [deleted] for xml raw ('Content'), elements xsinil) INSERT INTO [History] ([ChangedTable] ,[ReferenceGuid] ,[ChangedBy] ,[ChangedOn] ,[IsDelete] ,[Changes]) SELECT 'Content', ContentGuid, ChangedBy, getutcdate(), 1, @Changes FROM [deleted] END I have decided to use the "one history table for all table changes" method. The changes for a row is stored in a xml column which obviously limits the total size of columns in a table being tracked, but that is not a problem in my application. In general I like this set up, since I will be able to change the schema continuously without having to change the triggers, and since the application will probably evolve a lot over the coming months this is pretty important to me. EDIT: I should add that all the tables I will be tracking have a uniqueidentifier column. This makes it possible to related table rows in the different tables being tracked with their history. A few concerns with the above SQL: - Can the inserted/deleted table change between "SET @Changes = (select * ..." and the "INSERT INTO ..." statement, such that the data is not valid? If so, how to work around that? - If I were to (hypothetically) perform a "UPDATE [Content] SET [Data] = 'something'", not only is my update trigger called once for each row updated, but the XML added to the inserted row in the history table ([History].[Changes]) represent all the rows updated in the batch update. How do I get around this? Are there any other issues I should be aware of? Regards, Egil.
View Replies !
Trigger A Dataflow Based On Execute SQL Task Result
Hi, I have a 'Execute SQL Task' in my 'control flow', my 'Execute SQL Task' will return a value which I am assigning to a variable. Based on the value of the variable, I need to control my other flows. If the variable's value is 1 then I should invoke a dataflow, else I should write a failure error message in event viewer. Please could someone provide some inputs on how this can be done. 'Execute SQL Task' ----->value 1 ------>data flow to be executed 'Execute SQL Task' ----->value !=1 ------> write some error message in the event viewer and no tasks should be executed after that. Thanks raj
View Replies !
Trigger To Update Table Based On COUNT Of Column
Hello again, I'm hoping someone can help with with a task I've been given. I need to write a trigger which will act effectively as a method of automatically distributing of incoming call ticket records. See DDL below for creation of the Assignment table, which holds information on the call ticket workload. Code Snippet CREATE TABLE #Assignment (CallID INT IDENTITY(1500,1) PRIMARY KEY, AssignmentGroup VARCHAR(25), Assignee VARCHAR(25) ) GO INSERT #Assignment (AssignmentGroup, Assignee) VALUES ('Service Desk', 'Jim Smith') INSERT #Assignment (AssignmentGroup, Assignee) VALUES ('PC Support', 'Donald Duck') INSERT #Assignment (AssignmentGroup, Assignee) VALUES ('Service Desk', 'Joe Bloggs') INSERT #Assignment (AssignmentGroup, Assignee) VALUES ('Service Desk', 'Joe Bloggs') INSERT #Assignment (AssignmentGroup, Assignee) VALUES ('Service Desk', 'Joe Bloggs') INSERT #Assignment (AssignmentGroup, Assignee) VALUES ('PC Support', 'Donald Duck') INSERT #Assignment (AssignmentGroup, Assignee) VALUES ('PC Support', 'Mickey Mouse') GO SELECT COUNT(CallID) AS [Total Calls], AssignmentGroup, Assignee FROM #Assignment GROUP BY AssignmentGroup, Assignee ORDER BY COUNT(CallID) DESC , AssignmentGroup, Assignee What I need to do is write a trigger for on INSERT to automatically update the Assignee column with the name of the person who currently has the least active calls. For example, using the data above, the next PC Support call will go to Mickey Mouse, and the next two Service Desk calls will go to Jim Smith. So, the logic for the trigger would be UPDATE #Assignment SET Assignee = (SELECT Assignee FROM #Assignment WHERE COUNT(CallID) = MIN(COUNT(CallID)) But that's only the logic, and obviously it doesn't work with the syntax being nothing like correct. Does any one have an idea or pointers as to how I should go about this? Grateful for any advice, thanks matt
View Replies !
SQL Trigger - Email Data Based On Date Field
I need to send an email when my 'LastRunDate' field is 30 days old (i.e. It should send an email if the LastRunField = 7/21/2007). I would need to include the matching fields in the database (i.e. MachineID, Description, etc.) then update that field to todays date. I have a few values in the 'Frequency' field such as Daily, Monthly, Yearly. Daily would be 24 hrs, monthly 30 days, yearly 365 days from the lastrundate. I am new to T-SQL & need a good p[lace to start. Any sugesstions. Thanks.
View Replies !
|
TRACKER
