How to use/create triggers in sql server 2000

i have used triggers using the oracle as database,
but i'm not able create triggers in sql server, plz help
how to create triggers.Check out books online under CREATE TRIGGER, there are two different types,
INSTEAD OF (executes before the transaction) and AFTER (after the action and
in the transaction).
The trigger fires once for the statement and not per row, so if you where
inserting 5 rows the trigger would execute once and not 5 times, the rows
affected can be found in the 'inserted', 'deleted' tables.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"shiva" <bany.shanker@.gmail.com> wrote in message
news:1133161769.576911.208680@.f14g2000cwb.googlegroups.com...
>i have used triggers using the oracle as database,
> but i'm not able create triggers in sql server, plz help
> how to create triggers.
>|||Hi
You can either do this through Enterprise manager by right clicking the
table in the tree view and choosing all tasks/manage triggers or a more
versatile option is to use Query Analyser and create a script to create the
trigger. See Books online for the CREATE TRIGGER statement or at
http://msdn.microsoft.com/library/d... />
2_7eeq.asp
John
"shiva" wrote:

> i have used triggers using the oracle as database,
> but i'm not able create triggers in sql server, plz help
> how to create triggers.
>|||Look in the BOl for that, there are some good exmaples for that.
http://www.aspfaq.com/show.asp?id=2229
HTH, Jens Suessmeyer.

How to use the Thousand Seperators In Crystal Report 8.5

Hi!

I am developing a accounting application using visual basic and ms access.
then i use the crystal report 8.5 for report.

plz explain how to use the thousand seperator formula in number field.

like Rs.5,55,555.00

when i applied the thousand seperator formula

it should be displayed like this :

Rs 55,555,55.00

what can i do for these problem plz explain.

then one more doubt is there :

how to use the crystal report in asp page.

plz have u got any source plz send me.

thanks in advance.What is your desired output?
Rs 55,555,55.00 or 5,55,555.00?|||hi mathivanan!

i like the following format

indian currency format

Rs : 5,55,55,555.00

plz explain|||Create a formula having this code
StringVar Amt;
StringVar s;
NumberVar N;
Amt:=Replace(Totext({AmountField}),",","");
s:=left(Amt,len(Amt)-3);
N:=len(s);
Select N
case 4:
left(s,1)&","& right(s,3) & ".00"
case 5:
left(s,2)&","& right(s,3)& ".00"
case 6:
left(s,1)&","& mid(s,2,2) & "," & right(s,3)& ".00"
case 7:
left(s,2)&","& mid(s,3,2) & "," & right(s,3)& ".00"
case 8:
left(s,1)&","& mid(s,3,2) & "," & mid(s,6,2) & "," & right(s,3)& ".00"
case 9:
left(s,2)&","& mid(s,3,2) & "," & mid(s,6,2) & "," & right(s,3)& ".00"
default:
s;
and place it in the details section|||hi mathi!

when i am using the formula!
i got the error message

"The Formula Result Must be a Boolean"
here u also use ToText Function but its fired error.

what can i do for these problem plz explain.

StringVar Amt;
StringVar s;
NumberVar N;

Amt:=Replace(Totext({product_details.prod_price}),",","");
s:=left(Amt,len(Amt)-3);
N:=len(s);

Select N
case 4:
left(s,1)&","& right(s,3) & ".00"
case 5:
left(s,2)&","& right(s,3)& ".00"
case 6:
left(s,1)&","& mid(s,2,2) & "," & right(s,3)& ".00"
case 7:
left(s,2)&","& mid(s,3,2) & "," & right(s,3)& ".00"
case 8:
left(s,1)&","& mid(s,3,2) & "," & mid(s,6,2) & "," & right(s,3)& ".00"
case 9:
left(s,2)&","& mid(s,3,2) & "," & mid(s,6,2) & "," & right(s,3)& ".00"
default:s;|||It should work correctly
Check again whether you miss something in the code|||Hi MathiVanan!

your code is working properly!

thanking you.|||Well
How did you have problem initially?|||Hi Mathy!

actually i am applying the function to the price field object using

Field Object --> Format -->Number Format-->
Thousand Seperator -->Choose the Formula Editor

later i just create a new formula field and applying this code.
and then drag the formula field in my report page
it working properly.

thanks mathi!

muthu

How to use data to generate a report

Hi,
I have one problem in my project. Generating the report plz help me.

I have one table called Emp, which consists of fields, empid,tdate,attn,reason.

Emp
-
empid | tdate | attn | reason

2281 6/3/2006 Present null
2282 6/3/2006 Tour Hyderabad

2283 6/3/2006 Present null
2281 6/4/2006 Present null
2282 6/4/2006 Present null
2283 6/4/2006 Tour null

I want to generate a report as given below based on the date submitted by the user.

Frm Date: 6/3/2006 To Date: 6/4/2006

empid Present Tour Absent No.of working days
2281 2 2
2282 1 1 1
2283 1 1 1

Please write the query and reply back to me. ASAP.

Thanx in advance.

This is typically called crosstab reports. Here is how you will do it SQL 2000.

Select EmpId,
Sum(Case When Attn = 'Present' Then 1 ELSE 0 END) As "Present",
Sum(Case When Attn = 'Tour' Then 1 ELSE 0 END) As "Tour",
Sum(Case When Attn = 'Absent' Then 1 ELSE 0 END) As "Absent",
FROM Emp
WHERE tDate >= '3 Jun 2006' AND tDate <= '4 Jun 2006'
GROUP BY EmpId
ORDER BY EmpId

The key is the SUM(CASE..END) section. The CASE statement checks if the record's Attn field contains the given value. If it does then 1 is added to the sum, otherwise 0 is added. So for your data...

2281 6/3/2006 Present 1 for present, 0 for tour and 0 for absent
2282 6/3/2006 Tour 0 for present, 1 for tour and 0 for absent
2283 6/3/2006 Present 1 for present, 0 for tour and 0 for absent
2281 6/4/2006 Present 1 for present, 0 for tour and 0 for absent
2282 6/4/2006 Present 1 for present, 0 for tour and 0 for absent
2283 6/4/2006 Tour 0 for present, 1 for tour and 0 for absent

So, when you start aggregating the data on emp id, you will get the data you need. However, this type of solution is suitable for known columns, if you dynamic columns then you will have to check for other alternatives.

SQL 2005 has new PIVOT predicates that you can explore in books online. It simplifies the SQL a lot.

|||

Hi Badri Narayanan,

I'm glad that you have answered my question and suggested the PIVOT predicates method to me. I will learn PIVOT predicates and try to solve my problem in easy way. If you have any suggestions plz forward to me.

Thanx for response.

Take care, Bye.

Regards

Ram