code needed for a trigger

Does anyone have code for a trigger that when the user deletes the record trigger it to insert into another table? Thanks in advance and appreciate your help!I cant "write" code for you but I can steer in you in the right direction

you can do a

DECLARE @.var1 ..., @.var2...
SELECT
 @.var1=Col1
 @.var2=Col2
...
FROM
 DELETED
INSERT INTO <table> <cols> VALUES (@.var1, @.var2...)

|||I am sorry but i am really unfamiliar with the trigger functions. So if anyone can help i would appreciate it.|||Check out the DELETE TRIGGER portion of this article:Auditing Through Triggers.

Terri|||Exactly what i needed. Thank you very much!

code a hyperlink

Hi

I have developed a report using microsoft reporting services with certain fields

In my report the user enters name (which is a parameter) and the report is displayed

Inside my report. I have a field studentID which should be a link which when clicked should take me to a new report which is a report in extranet.

Currently I dont have access to that rdl and for certain reasons, I am asked to link to that report by

coding a hyperlink in development

I know that in the text box under action properties I need to give the url , but its not working

Should i specify the student id anywhere .

What and where should I code?

Thanks

If I were going about this, I would use the "Jump to URL" radio button.

However, the problem that you are going to have is passing and retrieving the parameter (as you already see).

Usually, the way you pass a parameter in a URL is as follows:

http://forums.microsoft.com/MSDN/AddPost.aspx?PostID=2009786&SiteID=1

The parameters in the above URL are PostID with value 2009786 and SiteID with value 1.

Now, how you go about retrieving those values in the report from the URL is beyond my experience.

Possibly someone else knows how.

COALESCE with parameters

I am trying to build a report table based on user supplied criteria at run time. The user may or may not enter criteria into one or more fields. I used the COLAESCE as follows (the temp vars may be passed valid data or left null by the user):

select * from dbo.employee

where LastName>=COALESCE(@.ln,lastname) andLastName<=COALESCE(@.ln2,lastname) andFirstName>=COALESCE(@.fn,firstname) andFirstName<=COALESCE(@.fn2,firstname) andhiredate>=COALESCE(@.hire,hiredate) andhiredate<=COALESCE(@.hire2,hiredate) andcheckdate>=COALESCE(@.chk,checkdate) andcheckdate<=COALESCE(@.chk2,checkdate)

The problem comes when I want to return rows that include columns that may be null. For example the CHECKDATE col might be the date the employee was reviewed and for new employees it may be null. I still want to return that row.

I had thought of creating default values for every column when the user adds a row to a table. I can set all char fields = ' ' and int fields = 0, but what is a valid default value for a date type col that won't cause problems when other procs try to grab the field and use it?

Or is there a better way to use the COALESCE function?

Thanks all!

I would add a third parameter to the coalesce function. For instance COALESCE(@.var,FieldName,'1/1/1900'). The third field would be the "default" value if the first two return null. HTH.

-Chris

|||If you couldn't choose some value as "empty", you could add additional parameter like @.ln_is_empty and then use something like (@.ln_is_empty=1 or (Lastname>=@.ln and @.ln_is_empty=0) )|||

Assuming that the query is contained within a stored procedure then you would have to start by declaring another input parameter per search term to indicate whether the WHERE condition for the relevant column should check for NULL or whether the search term should be ignored (i.e. equal to itself), currently it seems that you have no way to distinguish between the two.

Once this has been done you can use:

WHERE ((checkdate >= COALESCE(@.chk,checkdate) and checkdate <= COALESCE(@.chk2,checkdate))

OR (@.checkdateisnull = 1 AND checkdate IS NULL))

As an aside, with a query such as the one you have presented you are unlikely to see great performance. It might be better to dynamically create and execute a SQL string inside the stored procedure, forget using COALESCE inside the SQL string and include only what's actually needed in the WHERE clause - using COALESCE in the way that you have done is likely to lead to table / index scans and gives little scope for performance improvements by indexing.

Chris

|||

There are better ways to write this query than using COALESCE with column names. The above usage will negate use of any indexes on the columns. So you will be pretty much scanning the entire table for any combination of parameters. See the link below for various techniques that will help you solve the problem. Look for my name to see some techniques that use COALESCE/ISNULL but with better results. Erland also covers in detail other techniques that will help you get the best results.

http://www.sommarskog.se/dyn-search.html

|||

Thanks!!!

I'll be studying that document for quite some time!