Below is a MySql trigger to insert/update other table
Trigger in backend is very useful to report the input data in many different way.. This will be last step in performance tuning when the data double up drastically.
DROP trigger IF EXISTS "Trigger Name";
delimiter $$
CREATE TRIGGER "Trigger Name"
AFTER INSERT ON "Table Name - where the trigger will be fired on Insert/Update"
FOR EACH ROW
BEGIN
Declare l_count, l_topErrorCount INT;
Declare l_Top1Error, l_Top2Error, l_Top3Error VARCHAR(150);
//To determine where you need to update or insert the value in other table
SELECT COUNT(*) INTO l_topErrorCount FROM "Table Name"
WHERE Team = NEW.Team
AND Date = NEW.ErrorDate
AND Report = NEW.Report;
////
You Logic goes here.....
//Retrive Top Error...//
////
IF l_topErrorCount > 0 THEN
UPDATE "Table Name"
SET Top1Error= l_Top1Error,
Top2Error=l_Top2Error,
Top3Error=l_Top3Error
WHERE Team = NEW.Team
AND Date = NEW.ErrorDate
AND Report = NEW.Report;
ELSE
INSERT INTO "Table Name"
(Date, Team, Report, Top1Error, Top2Error, Top3Error)
VALUES(NEW.ErrorDate, NEW.Team, NEW.Report, l_Top1Error, l_Top2Error, l_Top3Error);
END IF;
END;
$$
How to Write bunch of text to a file
Here is one of the way to Write bunch of text to flat file:
1 2 3 4 5 6 7 8 9 10 11 12 | Public Sub WriteFile(ByVal sText As String, ByVal sFile As String, Optional ByVal bAppendMode As Boolean = True) Try Dim Stream_Writer As New IO.StreamWriter(sFile, bAppendMode) Stream_Writer.Write(sText & vbCrLf) Stream_Writer.Flush() Stream_Writer.Close() Catch ex As Exception 'Statements to handle Errors Finally End Try End Sub |
How to Check whether given file exists or not
Here is the code to Check whether the given file exists or not in VB.Net:
1 2 3 | Public Function IsFileExists(ByVal strFileName As String) As Boolean IsFileExists = System.IO.File.Exists(strFileName) End Function |
SQL Server – How to Configure Database mails
Below are the steps to configure the Database mailing feature in the SQL Server 2008 Database.
The mail can be triggered through a Stored Procedure call. The mails are queued and deliverd using the SMTP Push mail servers.
Steps:
1. In Sql Server database server, Go to Management --> Database Mail.
2. Right click the Database Mail and click on 'Configure Database mails'
3. First we need to create a 'Account' - Click on 'Create new Account'
Account Name :
Email Address : Mailing will be delivered with this mail id as 'From field'
Sever name : SMTP Server name
Port number : 25
Authentication : Basic/Windows (any valid account in the SMTP server domain)
4. Second we need to create a 'Profile' - Click on 'Create new Profile'
Profile Name :
SMTP accounts : Add the create account to the list
5. Now you could you the Profile created to trigger database mail from Stored Procedure,
You can use the below statement inside the SP to trigger the mail
EXEC msdb.dbo.sp_send_dbmail
@recipients='abc@gmail.com',
@body='Test message',
@sensitivity ='Personal',
@importance ='High',
@body_format ='HTML',
@subject ='Test mail',
@profile_name =
How to get System’s active printer name
Here is the code to get the System's active printer name:
1 2 3 4 | Private Function GetActivePrinter() As String Dim ps As New Drawing.Printing.PrinterSettings() Return ps.PrinterName End Function |