|
SQLServer comes with a rich BooksOnline both whilst installation and at MSDN Online. What more are you looking at?
|
|
|
|
|
I want to restrict user for not adding the new record if table already contains active record. (means Active=0)
CREATE TRIGGER TR_TB_QuizMaster ON TB_QuizMaster
FOR INSERT
AS
DECLARE @iCount int
BEGIN
SELECT @iCount=COUNT(*) FROM TB_QuizMaster WHERE Active=0
Print @iCount
IF (@iCount>=1)
BEGIN
Print 'Active record already exists. Only one active record can exisits'
Rollback
END
ELSE
print 'Inserted'
END
But this does not allow me to enter a new record unless i change it to IF (@iCount>=2). This is because it fires after insert.....Is there any way by which we can fire trigger BEFORE actual DML operation....
Please suggest
Thanks!!
-- modified at 6:49 Friday 21st September, 2007
|
|
|
|
|
There is - it's called an instead of trigger and works by defining a trigger on a view which matches the table structure. What happens is you insert into the view which runs the trigger and then depending on your result, inserts into the appropriate table. From memory, it would look something like this:
create trigger MyTableInsertCheck
on MyView
instead of insert
as
begin
select @count = count(1) from mytable m inner join inserted i on m.keyfield = i.keyfield
if @count = 0
begin
insert name, keyfield into mytable
select name, keyfield from myview
end
end
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks and if i don't want to create views....Is there any Before trigger in SQL 2005.....
|
|
|
|
|
Amit Kumar G wrote: Is there any Before trigger in SQL 2005.....
No (except as in the example above).
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
thanks!! I just read trigger syntax and would really appreciate if you could help me to understand difference between FOR|After clause....
|
|
|
|
|
I want to list dates with a simple select-statement like this.
2007-01-01
2007-01-02
2007-01-03
2007-01-04
2007-01-05
2007-01-06
2007-01-07
2007-01-08
2007-01-09
Any tip how to do that?
_____________________________
...and justice for all
APe
|
|
|
|
|
Do you want to return data that contains this date? If so, use a SELECT ... FROM ... WHERE DATE BETWEEN @startDate AND @endDate.
Alternatively, if you want to just retrieve the dates and are using SQL Server 2005 you could always use a Common Table Expression (CTE) as in:.
WITH DateIncrement (PreviousDate) AS
(
SELECT CAST('2007-09-01' AS DATETIME)
UNION ALL
SELECT DATEADD(d, 1, PreviousDate)
FROM DateIncrement
WHERE PreviousDate < '2007-10-01'
)
SELECT * FROM DateIncrement p As you can see, the CTE passes the start date in to the first part of the query and then does a union to add the incrementing date.
Finally, you could always write your own function to do this, but I prefer the CTE approach.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Perfect!!
WITH DateIncrement (PreviousDate) AS( <br />
SELECT CAST('2007-09-01' AS DATETIME) <br />
UNION ALL <br />
SELECT DATEADD(d, 1, PreviousDate) <br />
FROM DateIncrement <br />
WHERE PreviousDate < '2007-10-01')<br />
<br />
SELECT * FROM DateIncrement p
...was exactly what I wanted!
_____________________________
...and justice for all
APe
|
|
|
|
|
I'm glad it helped. CTEs are really useful for all sorts of hierarchical/recursive searches and people just don't have enough information about them.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Well, can they? Anybody tried this out? What should I look out for? Will my system burn up?
Any insights welcome.
Thank you in advance.
Chandra
|
|
|
|
|
You can have multiple instances of SS2000, you can only have one default instance, the others need to be named. This works just as well if you have multiple instances, but of different versions. Not a problem.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Logically. But so many Windows services and the number of SQLConnections it bears is a pain-on-the-neck for the server unless it has good amount of resources like multiprocessors and more RAM memory. Isn't it? Two should be good. Or any other views?
|
|
|
|
|
Thanks Christian... I will be installing SQL Express over the weekend then.
Another question, if I may - do you know of a way I can import an SQL Server DB into SQL Express? Is this at all possible?
Thanks
Chandra
|
|
|
|
|
Yes. I currently run both side by side and no ill effects.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
|
Hello friends, I want to connect remotely to sql server 2000 database using sqk server 2005. But I got error like this...
TITLE: Connect to Database Engine
------------------------------
Cannot connect to maximus.webdns.net.
------------------------------
ADDITIONAL INFORMATION:
This version of Microsoft SQL Server Management Studio Express can only be used to connect to SQL Server 2000 and SQL Server 2005 servers. (Microsoft.SqlServer.Express.ConnectionDlg)
------------------------------
BUTTONS:
OK
------------------------------
What is the solution for this? Plz help me........
|
|
|
|
|
What is the default return type of Stored procedures using in SQL?
Senthil S
Software Engineer
|
|
|
|
|
Stored procedure can return only int in SQL Server 2005
|
|
|
|
|
hi
Can u tell the exact position where its.
Otherwise tell the type is enough??????
Senthil S
Software Engineer
|
|
|
|
|
Senthil S wrote: Can u tell the exact position where its.
Otherwise tell the type is enough??????
Giorgi told you the answer. He said: "Stored procedure can return only int in SQL Server 2005"
|
|
|
|
|
Traditionally developers used TSQL to create SQL server stored procedures, functions and triggers. SQL Server 2005 has integrated support for .NET Common Language Runtime (CLR). That means you can now use .NET languages such as C# and VB.NET to create SQL server stored procedures, functions and triggers. This CLR integration provides various advantages ranging from compiled execution, security, type safety and enhanced programming model. In this article I am going to show how these features can be used for creating and consuming stored procedures.
Get it
SSK.
|
|
|
|
|
Hello
Sorry sir i am not able to get answer for my question.
U can tell me exact type of return value of stored procedure
Senthil S
Software Engineer
|
|
|
|
|
Once again, it's an int , Integer, System.Int32, 32-bit signed integer, 4 byte value, ...
|
|
|
|
|
Well - that's a nice answer that completely fails to address the question. It completely ignores the request while giving a nice bland (almost politician like) answer.
Deja View - the feeling that you've seen this post before.
|
|
|
|