In SQL Server, you can use the sys.server_triggers catalog view to return a list of server triggers.
More specifically, this view contains the set of all server-level DDL triggers with object_type of TR or TA.
For CLR triggers, the assembly must be loaded into the master database.
You can use the sys.triggers catalog view to return a list of triggers in a database in SQL Server.
This view contains a row for each object that is a trigger, with a type of TR or TA.
Continue readingI thought it would be interesting to run a few quick queries to see how various formatting strings affect the formatting of date/time values.
Some database tables include a “last modified” column, which stores the date and time that the row was last updated. Each time the row is updated, the date is updated to reflect the date and time of that update.
In SQL Server, you can use a trigger to perform this update.
A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server.
You can use the CREATE TRIGGER statement to create a trigger when using T-SQL. This statement can be used to create a DML, DDL, or logon trigger.
Below is a full list of date/time functions available in PostgreSQL.
Continue readingBelow is a full list of mathematical functions available in PostgreSQL.
These are arranged into the following four categories:
Are you’re trying to drop a logon trigger in SQL Server, but you’re getting an error like the following?
“Cannot drop the trigger ‘trigger_name’, because it does not exist or you do not have permission.”
It could be because you’re missing the ON ALL SERVER argument.
Students learning a new programming language will often start their first lesson with an IF statement, where their statement will return a value only if the expression is true.
They might then progress to an IF... ELSE statement, where they can determine another value to return if the expression is false. So therefore, return one value if the expression is true, and another value if it’s false.
SQL Server certainly includes the IF... ELSE statement in its T-SQL toolbox.
SQL Server also includes the IIF() function, which does a similar thing, but with a more concise syntax.
But there are some subtle differences.
Continue readingIn PostgreSQL, you can use the OVERLAPS operator to test for overlapping time periods.
The function returns true when two time periods (defined by their endpoints) overlap, and false when they do not overlap.
Continue readingTo format a number as currency in Postgres, you can either convert it to the money data type, or use to_char() to convert it to text that includes the appropriate currency symbol.
This obviously assumes that the number isn’t already stored using the money type.
Below are examples of each of these options.
Continue reading