Suppose also that the ACTIVITY table is already populated with several records, say with activity_id = 1, 2, and 3.
OBJECTIVE: When a new member record is added to MEMBER, say member_id 10, insert one record in the HEADCOUNT table for EACH activity in ACTIVITY for that member. Thus, if member #10 is added to MEMBER, then the trigger (or some other mechanism) would add the following records to HEADCOUNT (which, say, already has 30 records):
I've been advised that a trigger should do the trick for this, but as I'm totally new to SQL, I'll need some help. I'm guessing some iterating SQL command language might be required, but as I'm new to SQL, I don't know how to proceed.
Note that I'm building an ASP.NET application based on VB, and so records will be added to MEMBER through a tableadapter INSERT command. (Though I suspect this has no bearing on trigger behavior.)
Hi, I need to write a simple function to format the contents of the fields in my table. I bascially want to say that if the value in a field is below '0' then format the text in colour red and if the value in the field is 0 or above then format it in black. Obviously this can be done by writing an expression in each field but i would prefer to write a function - any ideas.....
I am trying to do following thing:i got a table which contains some words....all i need to do is whenever i see a word starting with 'R', i need to remove the starting 'R' and update it in the same table... could anyone help me out here.....thanks a lot in advance.........
Pdates - (here pid is foreign key field linked to diff table proposal)
pdate
pid
pkid-primary key
Cdates - (here cid is foreign key field linked to diff table confirm)
cdate
cid
tid
strttime
endtime
pkid-primary key
I wish to just copy the records from pdates to cdates selected based on pid value with diff cid. The other fields tid,strttime and endtime will be updated later.
Ok I have 2 batch files and I have rum them one after one another. I am using
call batch1.bat call batch2.bat
It is running the 1st batch fil successfully but it is not running the second one. I used a pause to see th e error it says some internal and external batch error.
Ok batch1 is at the desktop. and batch2 is in one of the folders in the desktop.
Nature of batch1 is that it runs successfully.
Nature of batch2 is that it gives and error if I individually execute it. But when I am running together then its not showing the error.l
Please if u did not understand this situation atleast show me how to run two batch files using command lines.
Please find the necessary SQL scripts to generate a small version of my database and some data at the bottom of this post.
Here's a short description of what the database is all about: It's a project tracking and management system. Contracts go into the tblDeals table. Because each project may be different in nature, project phases are defined in tblPhaseType and tblPhase tables. The table used to keep track of what's going on is the tblProduction table.
Here's what I need to do. When a project is completed -- meaning it has gone through all the phases that it needs to go through -- I want a trigger to fire up and change the contract status in the tblDeals table to "Completed" whose value is 1. When a new contract gets entered into the table, the Contract Status is set to 5 by default which means "In Progress" -- as defined in tblContractStatus. The tricky part is that because, each project is different and has different number of phases, the trigger has to make sure that all the phases have been submitted into the tblProduction table for that particular deal.
I'd really appreciate some help here. Thanks in advance for all your help.
--------------------------------------- Here's the script ---------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblCompany]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblCompany GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblContractStatus]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblContractStatus GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblPhaseType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblPhaseType GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblPhase_tblPhaseType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[tblPhase] DROP CONSTRAINT FK_tblPhase_tblPhaseType GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblProduction_tblDeals]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblDeals GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblProduction_tblPhase]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblPhase GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/20/2003 11:30:48 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblProduction]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblProduction] GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/20/2003 11:30:48 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDeals]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblDeals] GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/20/2003 11:30:48 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPhase]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblPhase] GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/20/2003 11:30:48 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCompany]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblCompany] GO
/****** Object: Table [dbo].[tblContractStatus] Script Date: 11/20/2003 11:30:48 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblContractStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblContractStatus] GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/20/2003 11:30:48 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPhaseType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblPhaseType] GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/20/2003 11:30:50 AM ******/ CREATE TABLE [dbo].[tblCompany] ( [CompanyID] [int] IDENTITY (1, 1) NOT NULL , [CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO
/****** Object: Table [dbo].[tblContractStatus] Script Date: 11/20/2003 11:30:50 AM ******/ CREATE TABLE [dbo].[tblContractStatus] ( [StatusID] [tinyint] IDENTITY (1, 1) NOT NULL , [Status] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/20/2003 11:30:51 AM ******/ CREATE TABLE [dbo].[tblPhaseType] ( [PhaseTypeID] [tinyint] IDENTITY (1, 1) NOT NULL , [Desription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/20/2003 11:30:51 AM ******/ CREATE TABLE [dbo].[tblDeals] ( [DealID] [int] IDENTITY (1, 1) NOT NULL , [CompanyID] [int] NOT NULL , [DealDate] [smalldatetime] NOT NULL , [PhaseTypeID] [tinyint] NOT NULL , [CashAmount] [smallmoney] NOT NULL , [StatusID] [tinyint] NOT NULL ) ON [PRIMARY] GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/20/2003 11:30:52 AM ******/ CREATE TABLE [dbo].[tblPhase] ( [PhaseID] [tinyint] IDENTITY (1, 1) NOT NULL , [PhaseTypeID] [tinyint] NOT NULL , [PhaseDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [PhasePercentage] [float] NOT NULL ) ON [PRIMARY] GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/20/2003 11:30:52 AM ******/ CREATE TABLE [dbo].[tblProduction] ( [TransactionID] [int] IDENTITY (1, 1) NOT NULL , [DealID] [int] NOT NULL , [PhaseID] [tinyint] NOT NULL , [TransactionTimeStamp] [smalldatetime] NOT NULL , [Comments] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[tblCompany] WITH NOCHECK ADD CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED ( [CompanyID] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[tblContractStatus] WITH NOCHECK ADD CONSTRAINT [PK_tblContractStatus] PRIMARY KEY CLUSTERED ( [StatusID] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[tblPhaseType] WITH NOCHECK ADD CONSTRAINT [PK_tblPhaseType] PRIMARY KEY CLUSTERED ( [PhaseTypeID] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[tblDeals] WITH NOCHECK ADD CONSTRAINT [PK_tblDeals] PRIMARY KEY CLUSTERED ( [DealID] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[tblPhase] WITH NOCHECK ADD CONSTRAINT [PK_tblPhase] PRIMARY KEY CLUSTERED ( [PhaseID] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[tblProduction] WITH NOCHECK ADD CONSTRAINT [PK_tblProduction] PRIMARY KEY CLUSTERED ( [TransactionID] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[tblDeals] ADD CONSTRAINT [DF_tblDeals_StatusID] DEFAULT (5) FOR [StatusID] GO
ALTER TABLE [dbo].[tblProduction] ADD CONSTRAINT [DF_tblProduction_TransactionTimeStamp] DEFAULT (getdate()) FOR [TransactionTimeStamp] GO
exec sp_addextendedproperty N'MS_Description', N'Determines the type of phase structure this deal will go through', N'user', N'dbo', N'table', N'tblDeals', N'column', N'PhaseTypeID' GO exec sp_addextendedproperty N'MS_Description', N'Identifies the current status of deal', N'user', N'dbo', N'table', N'tblDeals', N'column', N'StatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the percentage value of the phase', N'user', N'dbo', N'table', N'tblPhase', N'column', N'PhasePercentage'
--------------------------------------- And here's some data ---------------------------------------
INSERT INTO [tblPhaseType] ([Desription])VALUES('TV Commercial - 4 Phases') INSERT INTO [tblPhaseType] ([Desription])VALUES('Full Campaign - 6 Phases')
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Customer Info',1.500000000000000e-001) INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Write script',2.500000000000000e-001) INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Shoot',3.500000000000000e-001) INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Edit commercial',2.500000000000000e-001) INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Customer info',1.500000000000000e-001) INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Write script',1.500000000000000e-001) INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Design print ad',1.500000000000000e-001) INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Shoot',1.500000000000000e-001) INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Edit',2.000000000000000e-001) INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Publish',2.000000000000000e-001)
INSERT INTO [tblContractStatus] ([Status])VALUES('Completed') INSERT INTO [tblContractStatus] ([Status])VALUES('Hold') INSERT INTO [tblContractStatus] ([Status])VALUES('Collections') INSERT INTO [tblContractStatus] ([Status])VALUES('Legal') INSERT INTO [tblContractStatus] ([Status])VALUES('In Progress')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Johnny''s Remodeling') INSERT INTO [tblCompany] ([CompanyName])VALUES('Perfect Cut Lawncare') INSERT INTO [tblCompany] ([CompanyName])VALUES('Useless Ideas Unlimited') INSERT INTO [tblCompany] ([CompanyName])VALUES('Try-It-Again, Inc.')
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(1,'Aug 5 2003 12:00:00:000AM',1,120.0000,5) INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(2,'Sep 9 2003 12:00:00:000AM',2,150.0000,5) INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(3,'Sep 10 2003 12:00:00:000AM',2,130.0000,5) INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(4,'Nov 20 2003 12:00:00:000AM',1,190.0000,5)
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,1,'Nov 10 2003 10:23:00:000AM','Received company logo') INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,2,'Nov 10 2003 10:23:00:000AM','Finished writing script') INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,5,'Nov 10 2003 10:23:00:000AM','Just received company info') INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,7,'Nov 10 2003 10:24:00:000AM','Finished designing ad copy') INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,3,'Nov 20 2003 11:29:00:000AM','Did more work') INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,4,'Nov 20 2003 11:29:00:000AM','Finally finished the job')
Any help appreciated! Is there any performance enhancements to be gained by storing frequently 'trigger-written-to' databases on a seperate disk to the source database? In particular, we keep a 'history' database of all inserts/updates/deletes against records, activated by triggers, and I was wondering if I would gain performance enhancement by locating the two databases on different disks? Thanks in advance
I am developing an application in vb.net 2005 using SQL Server 2000. In this I have two tables SessionMaster and SessionChild. Fields of session master - SessionMastId, Start_Date, End_Date, Session_Type, Fields of session child - SessionChildId, SessionMastId, UserName, Comment. SessionMastId and SessionChildId are primary keys of respective tables and also they are auto increment fields. Please how to write trigger to insert record into both tables at a time.
Hiim trying to implement a simple trigger, i had it working fine in oracle but am finding it difficult to convert it to the MS SQL 2005 layout.I have two tables:1) don (columns A,B,C)2) cur (columns A,B)Basically i just want to insert the value of A and B from table don into table cur after an insert.this is what i have:________________________________ALTER TRIGGER [TG_doncur] ON don AFTER INSERTAS BEGININSERT curSET A = A, B=BEND_______________________________i have an if clause aswell but i just wanna get the basics first.any help would be greatBil
I get this error message when I try to connect to Reporting Services via the Management Studio.
I can see my machine listed in the Server Name > Browse For More > Local Servers dialogue. But no luck,
Ive tried:
Servername: localhost Servername: DED1774 (the machine name) Servername: localhost/reportserver Servername: DED1774/reportserver Servername: http://ded1774/reportserver (from the rsreportserver.config file
<UrlRoot>http://ded1774/reportserver</UrlRoot>)
I've Googled the error message and found postings for solutions, but none of these helped. Can anyone suggest some simple steps I can take to try to find the issue and get the connection working?
I am looking at a table in Microsoft SQL Server. I went to thedependencies of this table and it says TRIG_customer. so i amthinking there is a trigger that affects the table but how do i seewhat is the code that resides within this trigger. I looked among thestored procedures but i couldnt find this trigger.are all the triggers listed together somewhere. where is this triggernamed TRIG_customer?thanks in advance
I do not know if I am in the proper thread, if not thnaks to let me know where to post it..
I have a runing table name CURENTALARM which strore different alarm information. This table has a Column named STATUS.. When the new inserted rows ocurrs and STATUS =1, then I need to copy that row in a new table name STATUSLOG...
For that I have created a trigger for table CURENTALARM and then do proper commands to insert to other table.
I am using the inserted table in my trigger to fetch last inserted rows.
The question I have is that how to guaranty that each inserted row will fire the triggers properly... What I mean is that in case I have 2 rows which gets inserted within less than a second in time interval, does the triggers will be able to do its job and proceed properly inserted row or is there a situation that when rows gets inserted too fast, the triger might miss some of them ?
I'm getting this error when I try to use "inserted" table in my create trigger call.
--------------------------- Microsoft Development Environment --------------------------- ADO error: The column prefix 'inserted' does not match with a table name or alias name used in the query.
--------------------------- OK Help ---------------------------
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
I'm getting this error when I try to use "inserted" table in my create trigger call.
--------------------------- Microsoft Development Environment --------------------------- ADO error: The column prefix 'inserted' does not match with a table name or alias name used in the query.
--------------------------- OK Help ---------------------------
Hi,We have been using ADO and the AddNew method for a long time as a meansto add records to the database. It has always worked fine - no problem.But - we recently started using INSERT triggers that simply call a fewstored procs (they're actually SSNS stored procs that send new eventinfo to notification services). Anyway, these triggers do not seem tofire at all! If I execute an insert command manually from QueryAnalyser, no problems. But the trigger does not fire at all from myapplication!Does anyone know why this could be? For info, my connection string usedby the ADO connection object looks like this: Provider=SQLOLEDB.1;DataSource=XXX;Initial Catalog=YYYAnd my AddRecord ADO code looks like this:With rs.Open sSQL, ConnectionString, adOpenKeyset, adLockOptimistic,adCmdTable And adExecuteNoRecords.AddNewAm I mnissing something obvious here? Any help appreciated!
Hello,I am trying to learn SQL Server. I need to write a trigger whichdeletes positions of the document depending on the movement type.Here's my code:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoCREATE TRIGGER [DeleteDocument]ON [dbo].[Documents]AFTER DELETEASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'DELETE FROM PositionsPZZWWHERE Documents.Number IN (SELECT Number FROM deleted);IF Documents.Movement = 'WZ' OR Documents.Movement = 'RW'DELETE FROM PositionsWZRWWHERE Documents.Number IN (SELECT Number FROM deleted);IF Documents.Ruch = 'MM'DELETE FROM PositionsMMWHERE Documents.Number IN (SELECT Number FROM deleted);ENDUnfortunatelly I receive errors which I don't understand:Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 12The multi-part identifier "Documents.Movement" could not be bound.Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 12The multi-part identifier "Documents.Movement" could not be bound.Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 13The multi-part identifier "Documents.Numer" could not be bound.Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 15The multi-part identifier "Documents.Movement" could not be bound.Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 15The multi-part identifier "Documents.Movement" could not be bound.Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 16The multi-part identifier "Documents.Number" could not be bound.Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 18The multi-part identifier "Documents.Movement" could not be bound.Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 19The multi-part identifier "Dokuments.Number" could not be bound.Please help to correct the code.Thank you very much!/RAM/
I am extremely new at SQL Server2000 and t-sql and I'm looking tocreate a simple trigger. For explanation sake, let's say I have 3columns in one table ... Col_1, Col_2 and Col_3. The data type forCol_1 and Col_2 are bit and Col_3 is char. I want to set a trigger onCol_2 to compare Col_1 to Col_2 when Col_2 is updated and if they'rethe same, set the value on Col_3 to "Completed". Can someone pleasehelp me?Thanks,Justin
I have just one table but need to create a trigger that takes place after an update on the Orders table. I need it to multiply two columns and populate the 3rd column (total cost) with the result as so:
I've this tableCREATE TABLE [dbo].[Attivita]( [IDAttivita] [int] IDENTITY(1,1) NOT NULL, [IDOwner] [int] NULL, [IDAttivitaStato] [varchar](1) COLLATE Latin1_General_CI_AS NULL, [IDAttivitaTipo] [varchar](2) COLLATE Latin1_General_CI_AS NULL, [IDAnagrafica] [int] NULL, [Data] [datetime] NULL CONSTRAINT [DF_Attivita_Data] DEFAULT (getdate()), [Descrizione] [varchar](max) COLLATE Latin1_General_CI_AS NULL, [Privato] [bit] NULL, [LastUpdate] [datetime] NULL CONSTRAINT [DF_Attivita_LastUpdate] DEFAULT (getdate()), CONSTRAINT [PK_Attivita] PRIMARY KEY CLUSTERED ( [IDAttivita] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]How Can I create a trigger for modify the IDAttività Stato field in specific situaion: if IDAttivitaTipo == OK or == NOIDAttivitaStato must be set to CPlease note that I can write in the IDAttivitaStato field (during my code operation).... but I would like also automatic update if the IDAttivitaTipo is OK or NO Can you help me for create this with a trigger?Thanks
I need a trigger to know who and when a char(1) column is changed. Â Would like to write the audit trail to its own table I can query and record before and after values.
DDL:
CREATE TABLE [dbo].[Test]( [Customer] [varchar](12) NULL, [Active] [char](1) NULL DEFAULT ('N') --Must use char 1 b/c more than 2 possible values )
Insert into Test (Customer, Active) Values ('Acme','Y')..I want trigger to tell me whowhenwhere this value was changed. Â If using sql auth capture client windows id if possible and write to audit table Update Test set Active = 'N'
Okay, I'm a novice! I installed Back Office with NT opperating system on my home computer. I want to learn SQL 7.0. This is incredible, but I can't launch the program. I've tried everything under Start, Programs, SQL, but nothing listed seems to get me into SQL 7.0. What am I missing? Help is most appreciated!!
public int RunSQLQuery(string sqlStatement) { if (_sqlConnection.State != ConnectionState.Open) _sqlConnection.Open();
_sqlCommand = new SqlCommand(sqlStatement,_sqlConnection); return _sqlCommand.ExecuteNonQuery(); }
Now i am stuck on how i will get the ID from the sp (that is @ID) and return it to my C# code as i need it to update the same row further on :)all i am getting till now is the number of rows 'changed' i.e. 1! any thoughts? thanks
Hi,I have quite a complicated request to do in sql.I've got on table with 3 fields (id, field1, field2) and I have toprocess a request on each of the records, one by one, and thendetermine if the status is OK for each recordsFor example, I would check if the sum of field1 and field2 is greaterthan 10.What is the best way to do this ? It has to be done in a storedprocedure. I can't return the status for each one of the records, soshould I store it in a temporary table and do a select on it from mytier application (vb.net) ?ThxSam
Hi,I'm going to explain as clearly as possible:I have two tables:Relationships(relation_id, table1, table2)Relationfields(relation_id, field1, field2)In Relationships, relation_id is the primary keyIn Relationfields, relation_id is the foreign keyI have a front-end interface that allows the user to add records toRelationships and Relationfields as followed:The user selects a table1 and table2 values from listboxes. These arereal table names from sys.objects, so then the user can select fieldsof these tables on which he wants to create a JOIN.Anyway, I can easily insert the table1 and table2 into Relationships(relation_id is an auto-increment). Then I need to get the relation_idof this new Relationship (easy since I know which values I've insertedand table1-table2 associations are unique.Now the PROBLEM :I need to insert into Relationfields all the fields selectioned by theuser for each of the two tables . But the user might have selectedseveral fields from table1 and table2, so I need to pass A LISTPARAMETER to my Stored Procedure as I don't know how many values offield1 and field2 there is going to be.I hope this is clear enough. Is it possible to achieve what I want ?Should I pass an entire concatenated string with values separated bycomma or whatever and then decrypt it in the stored procedure ?Thx
I've got a table that houses the data for several routes, (routeID, pointID, Longitude, Latitude and Elevation). a set of Points make up a route. I'd like to programmatically access specific points and I'm trying to figure out how to request...say the third point in my dataset. I'm new to SQL, but I was able to figure out that I can find the row number by using the SQL syntax:SELECT ROW_NUMBER() OVER(ORDER by PointID) as 'Num', Latitude, Longitude, Elevation FROM [PointTable] WHERE (RouteID = 5) But I cannot (or do not know how to) add a clause that saysAND (Num = 3) So can someone show me how to request a specific row?
I have two tables:-------- Bids-------PKBidsFKAuctionsFKUsersBidAmount-------------------FollowedLots-------------------PKFollowedLotsFKAuctionsFKUsers Fields beginning with PK are Primary keys and those beginning with FK are Foreign Keys. For each FollowedLot of a specific User, I would like the MAX BidAmount of the FKAuctions and also the MAX BidAmount of the FKAuctions of that specific User. I hope you can understand my question.Thank you!
